1
00:00:01,100 --> 00:00:07,190
The last two lessons introduced the concept of DACs context and demonstrated how formulas calculate

2
00:00:07,190 --> 00:00:12,110
differently, based both upon how you have laid out your table and structured your formulas.

3
00:00:13,220 --> 00:00:18,570
One thing that probably jumped out to you is how cramped and difficult those formulas seem to be.

4
00:00:18,950 --> 00:00:20,930
Remember this cash on hand measure?

5
00:00:22,220 --> 00:00:25,550
Relatively speaking, this tax formula is pretty basic.

6
00:00:25,580 --> 00:00:31,520
However, it's still difficult to read and manage the parentheses alone are daunting, but never fear

7
00:00:31,790 --> 00:00:35,750
we can break our measures out into multiple steps using variables.

8
00:00:36,880 --> 00:00:44,200
I can break this formula into three distinct easy to follow steps that separate out our logic, but

9
00:00:44,200 --> 00:00:50,140
before I do so, let me introduce the VA and return keywords using a new simple measure.

10
00:00:52,050 --> 00:00:58,320
This new measure will add one and two together to form three, as you know, I could do this by simply

11
00:00:58,320 --> 00:01:00,360
inputting one plus two.

12
00:01:00,810 --> 00:01:06,630
But let's imagine that one and two are highly complex calculations that look more like our cash on hand

13
00:01:06,630 --> 00:01:08,550
formula than just one or two.

14
00:01:10,100 --> 00:01:14,720
I'm going to break this out into a pair of variables that we then add together separately.

15
00:01:15,790 --> 00:01:22,240
My first line will start with a VA keyboard, followed by a name I like to proceed my variable names

16
00:01:22,270 --> 00:01:27,460
with an underscore for reasons that I will explain shortly, but this is not required.

17
00:01:28,120 --> 00:01:33,490
After the variable name, I set an equal sign and I can write a formula to define that variable.

18
00:01:35,930 --> 00:01:43,130
For the simple measure, I'm going to type var underscore left value equals one, this statement creates

19
00:01:43,130 --> 00:01:46,250
a left value local variable that is equal to one.

20
00:01:47,330 --> 00:01:53,090
I can only use this left value within this measure, no other measures will be able to see it.

21
00:01:53,210 --> 00:01:55,100
So you can think of it as temporary.

22
00:01:56,960 --> 00:02:03,240
I'm going to add another variable var underscore right value equals two on the next line.

23
00:02:04,010 --> 00:02:06,380
I now have two variables within my measure.

24
00:02:07,750 --> 00:02:13,630
If I check my formula, I get an error, since the formula doesn't know what the result should be and

25
00:02:13,630 --> 00:02:16,540
tells us that the expression appears to be incomplete.

26
00:02:18,010 --> 00:02:23,440
I need to tell the formula what value to return, which will involve the return keyword.

27
00:02:24,420 --> 00:02:30,960
I'll start my next line with return and go immediately into writing whatever formula it is that I want

28
00:02:30,960 --> 00:02:32,670
to return as my result.

29
00:02:33,610 --> 00:02:39,820
In this case, I'm going to type underscore and you'll see the entire text pop up, since my measures

30
00:02:39,820 --> 00:02:45,730
table is preceded by underscore, along with my local variables, this essentially contains a list of

31
00:02:45,730 --> 00:02:48,520
the possible items that I may want to return.

32
00:02:49,940 --> 00:02:56,720
For this particular case, I want underscore left values for all, type it out, and then you'll see

33
00:02:57,230 --> 00:03:02,010
that the local variable definitions take on a blue text color in the tax formula.

34
00:03:02,030 --> 00:03:02,540
Ed.

35
00:03:03,460 --> 00:03:08,020
I'll add a plus sign and then underscore right value to finish out my logic.

36
00:03:09,390 --> 00:03:14,610
Now, when I click on check formula, it tells me that the formula is correct and I can move on.

37
00:03:15,480 --> 00:03:21,750
The measure is complete, but I personally prefer not to include any logic in the return line, so I

38
00:03:21,750 --> 00:03:24,480
would rewrite this measure with yet another variable.

39
00:03:25,020 --> 00:03:30,420
Far underscore result equals underscore left value, plus underscore right value.

40
00:03:31,290 --> 00:03:35,220
I can replace my return logic with simply underscore result.

41
00:03:37,120 --> 00:03:42,840
So I've just gone through this process to create a really complex formula to add one and two together,

42
00:03:43,420 --> 00:03:47,530
but how can I use this to improve our process for some of our past formulas?

43
00:03:50,260 --> 00:03:56,770
If you remember from our past videos, we did try using a separate measure for the Max calendar date

44
00:03:56,950 --> 00:04:00,130
to simplify our cash on hand, but it didn't work.

45
00:04:01,130 --> 00:04:06,830
Because our local variables are calculated within the same context, we can use a local variable to

46
00:04:06,830 --> 00:04:09,290
simplify the logic the way that we'd wanted to.

47
00:04:10,550 --> 00:04:17,150
Additionally, if you happened to be building this from scratch, variables are a great way to plot

48
00:04:17,150 --> 00:04:21,620
out and manage complex logic involving your context management.

49
00:04:22,460 --> 00:04:28,760
This is particularly true for measures involving the functions that may involve multiple sub filtered

50
00:04:28,760 --> 00:04:30,680
sets that are cross joined together.

51
00:04:32,290 --> 00:04:38,110
The first logical component I'm going to define here is the end of my time frame, so I'll create a

52
00:04:38,110 --> 00:04:43,030
VA underscore and of time frame equal to max calendar date.

53
00:04:45,310 --> 00:04:50,680
This will capture the maximum calender date in the context for this measure, unadjusted for any other

54
00:04:50,680 --> 00:04:51,880
filter adjustments.

55
00:04:53,130 --> 00:04:59,460
The next logical component I want is the adjusted calendar period, the context for our calendar at

56
00:04:59,460 --> 00:05:05,000
the start of this measure is whatever subset of dates falls within the scope of our current sell.

57
00:05:05,550 --> 00:05:12,300
However, to calculate our cash on hand correctly, we need to include all of the dates that occur prior

58
00:05:12,300 --> 00:05:16,320
to the first date of our scope, in addition to those dates within our scope.

59
00:05:17,540 --> 00:05:23,480
We did this originally with a filter all and then filtered subject to the maximum calender date.

60
00:05:24,800 --> 00:05:30,560
I can perform this step within another variable, using the filter function almost identically to how

61
00:05:30,560 --> 00:05:39,590
we did it before var underscore adjusted Callendar equals filter all calendar and then subject to the

62
00:05:39,590 --> 00:05:44,870
calendar date being less than or equal to the underscore and of our time frame variable.

63
00:05:47,000 --> 00:05:53,570
These two variables have broken out our logic, so that is now easier to follow step by step without

64
00:05:53,570 --> 00:05:55,430
digging through all of the parentheses.

65
00:05:56,810 --> 00:06:04,910
I can finalize the measure by defining my VA underscore result as the calculate profit subject to the

66
00:06:04,910 --> 00:06:06,080
adjusted calendar.

67
00:06:07,290 --> 00:06:13,080
This formula is longer and more worthy than our previous one, but I have compartmentalized the logic

68
00:06:13,230 --> 00:06:16,050
into smaller and easier to understand tidbits.

69
00:06:17,550 --> 00:06:20,640
Using local variables won't always be the right answer.

70
00:06:20,880 --> 00:06:25,000
There are some cases where having separate measures has its own benefits.

71
00:06:25,590 --> 00:06:31,920
However, local variables can allow you to greatly simplify code from something that is simply unreadable

72
00:06:31,920 --> 00:06:36,090
and incredibly difficult right into much more manageable chunks.

73
00:06:36,960 --> 00:06:38,340
Here is one example.

74
00:06:39,090 --> 00:06:46,560
This particular chunk of DACS is from a project of my own whereby I needed to identify how many customers

75
00:06:46,710 --> 00:06:52,410
with active contracts would opt to upgrade their contract from one type to another, given different

76
00:06:52,410 --> 00:06:57,180
probabilities of converting at different times based upon how old their contract is.

77
00:06:59,040 --> 00:07:03,810
Technically, this could have been done in one line of code, but that would have been incredibly difficult

78
00:07:03,810 --> 00:07:04,980
to write and manage.

79
00:07:06,110 --> 00:07:12,380
Initially, you may not find these variables very useful, however, as you progress in the more advanced

80
00:07:12,380 --> 00:07:18,170
DACS computations like the one here, you'll find yourself using them more and more frequently.