1
00:00:01,380 --> 00:00:06,930
In the last lesson, I introduced you to measures and how to write some basic ones with a brief short

2
00:00:06,930 --> 00:00:13,110
introduction to more advanced measures, of course, in writing that advanced measure, we wrote a fairly

3
00:00:13,110 --> 00:00:18,670
long formula involving a sum that we already had, plus some other filter adjustments to our data.

4
00:00:19,530 --> 00:00:22,320
So let's go back and look at it and begin breaking it down.

5
00:00:23,280 --> 00:00:28,590
First, the calculate function will be a very, very common friend and ally as it's used with measures

6
00:00:28,590 --> 00:00:31,380
to modify and adjust context for many different needs.

7
00:00:32,290 --> 00:00:37,450
In this case, we've created a custom measure for our calculation, the sum of sales, but what if we

8
00:00:37,450 --> 00:00:40,510
wanted something more complicated than just the sum of our sales here?

9
00:00:40,510 --> 00:00:44,680
Or what if our sales measure itself needed to change?

10
00:00:44,680 --> 00:00:50,140
For some reason, with the way our current measures are handled, we would have to adjust both our sales

11
00:00:50,140 --> 00:00:55,780
total cumulative and our sales total measures, which would be a hassle and would be prone to errors.

12
00:00:56,350 --> 00:00:58,990
We can be smart about this and change our measures together.

13
00:00:59,470 --> 00:01:05,500
As our first example, I can simply replace this some sales sales with the sales total measure.

14
00:01:05,500 --> 00:01:10,540
Since it is the sum of the sales, this bill simplifies our current formula so that we don't have a

15
00:01:10,540 --> 00:01:16,300
formula within a formula and it structures our cumulative calculation to include any adjustments that

16
00:01:16,300 --> 00:01:17,260
we make to our sales.

17
00:01:17,260 --> 00:01:18,870
Total calculation function.

18
00:01:19,360 --> 00:01:24,520
Suppose, for example, that I go to my sales total and I change it from something sales to something

19
00:01:24,550 --> 00:01:31,570
sales times to now both my sales total and my sales total, cumulative computation stay in sync without

20
00:01:31,570 --> 00:01:34,480
me having to update the cumulative calculations separately.

21
00:01:35,050 --> 00:01:38,750
This is pretty handy, but it doesn't always work quite so conveniently.

22
00:01:39,280 --> 00:01:42,270
Let's go back to this cumulative measure and take a look at it again.

23
00:01:42,700 --> 00:01:47,360
We have what appears to be another measure in our max calendar date function area.

24
00:01:48,040 --> 00:01:53,470
This clearly looks like another opportunity to have a measure called Maximum Date Calculated and then

25
00:01:53,470 --> 00:01:54,130
use it here.

26
00:01:55,100 --> 00:01:57,380
Let's go create that max tape measure, shall we?

27
00:01:57,430 --> 00:01:58,780
I'll call it maximum date.

28
00:01:59,500 --> 00:02:06,010
Make take the maximum of the date and we'll apply it when I add it into our cumulative function and

29
00:02:06,010 --> 00:02:07,770
close out, what do we see?

30
00:02:07,780 --> 00:02:11,950
Well, our cumulative function no longer works or what's happened.

31
00:02:12,430 --> 00:02:17,980
As it turns out, you can't use predefined measures when performing a comparison like this in a filter

32
00:02:17,980 --> 00:02:18,550
function.

33
00:02:18,940 --> 00:02:21,100
We need to use the formula computation.

34
00:02:21,400 --> 00:02:23,020
The technical reasons elude me.

35
00:02:23,170 --> 00:02:29,380
However, my general rule of thumb is to use the specific calculation rather than a separate measure

36
00:02:29,530 --> 00:02:32,770
whenever applying a filter through this formula approach.

37
00:02:33,990 --> 00:02:40,050
Now, outside of applying a filter, it's a very good practice to use compound measures when possible

38
00:02:40,050 --> 00:02:46,950
to keep them in sync, for example, of that we might not really be interested in the cumulative sales

39
00:02:46,950 --> 00:02:53,520
or purchase orders or wages, but we would be very interested in the cumulative running cash status

40
00:02:53,520 --> 00:02:57,750
of the business, which would be the cumulative profit at any point in time.

41
00:02:58,140 --> 00:03:04,980
To calculate this, we could calculate the cumulative of all three of our other fields, or we could

42
00:03:04,980 --> 00:03:07,500
just use the profit field that we've already created.

43
00:03:07,990 --> 00:03:09,720
I've created this measure already.

44
00:03:09,720 --> 00:03:12,300
I've called it cash on hand and we can go take a look.

45
00:03:13,190 --> 00:03:18,710
It's very simple, it's the same pattern as we used in our sales total, but instead of using the sales

46
00:03:18,710 --> 00:03:20,480
total, we've used the profit measure.

47
00:03:20,870 --> 00:03:25,790
The profit measure crosses multiple tables, but the context is the same here that we need.

48
00:03:26,360 --> 00:03:29,600
So let's hop out to this pivot table and shuffle some fields around.

49
00:03:29,870 --> 00:03:34,610
I'm going to drop the quarter in exchange for the date hierarchy and I'm going to add cash on hand.

50
00:03:35,660 --> 00:03:40,100
Now, at this level doesn't look special since we have our obvious profit and cash on hand totals for

51
00:03:40,100 --> 00:03:41,300
all of twenty nineteen.

52
00:03:41,660 --> 00:03:45,980
But let's expand twenty nineteen to months and then I'll expand January two days.

53
00:03:46,460 --> 00:03:52,460
Within this profit measure, which is a very common tax pattern, we've created a pretty useful output

54
00:03:52,460 --> 00:03:56,260
that lets us figure out the first point in time in which we break even on cash flow.

55
00:03:56,600 --> 00:04:00,560
We can see that Albertsons and Kroger's are cash flow positive by January.

56
00:04:00,560 --> 00:04:01,910
Twenty three twenty nineteen.

57
00:04:02,540 --> 00:04:03,740
But this isn't far enough.

58
00:04:03,740 --> 00:04:05,420
Let's enhance this a little bit more.

59
00:04:05,870 --> 00:04:12,800
Let's suppose that the annual rate the company can borrow cash at is twenty five percent with simple

60
00:04:12,800 --> 00:04:13,310
interest.

61
00:04:13,730 --> 00:04:18,860
Basically the interest doesn't compound so I can calculate the interest at it in any given day.

62
00:04:19,130 --> 00:04:20,420
Using cash on hand.

63
00:04:20,420 --> 00:04:26,600
Ten zero point twenty five divided by three hundred sixty five at least with my cash on hand is negative

64
00:04:27,530 --> 00:04:30,020
so I can create a new measure pretty simply like that.

65
00:04:30,020 --> 00:04:30,380
Right.

66
00:04:30,830 --> 00:04:31,640
New measure.

67
00:04:31,640 --> 00:04:38,870
Interest expense is the name use a formula equals if cash on hand less than or equal to zero cash on

68
00:04:38,870 --> 00:04:42,770
hand times zero point twenty five divided by three sixty five comma zero.

69
00:04:42,800 --> 00:04:43,280
Easy.

70
00:04:43,670 --> 00:04:46,940
So let's add that to the pivot table and voila it's there.

71
00:04:46,940 --> 00:04:48,560
We have our interest in each day.

72
00:04:49,070 --> 00:04:51,560
But let's take a step back.

73
00:04:51,950 --> 00:04:54,380
We're using cash on hand in a comparison here.

74
00:04:54,380 --> 00:04:56,180
I didn't think we could do that.

75
00:04:56,810 --> 00:05:02,550
Well, what do you think happens when we condense our table and we look at the month and whole?

76
00:05:02,960 --> 00:05:05,270
We obviously have interest expenses in January.

77
00:05:05,270 --> 00:05:06,080
We see them here.

78
00:05:06,110 --> 00:05:07,760
It'll just sum up the total, right?

79
00:05:08,600 --> 00:05:10,610
Well, why is our total here zero then?

80
00:05:10,610 --> 00:05:12,320
That's obviously incorrect.

81
00:05:12,950 --> 00:05:17,450
Our measure computations calculate at the contexts aggregate level.

82
00:05:18,890 --> 00:05:24,500
So the aggregate level here where it does if cash on hand is less than equal, zero sees the cash on

83
00:05:24,500 --> 00:05:25,450
hand is positive.

84
00:05:25,460 --> 00:05:28,490
So it gives us a zero, therefore no interest expenses.

85
00:05:29,090 --> 00:05:34,580
You might think something like maybe adding a sum to our measure would help or using calculate and applying

86
00:05:34,580 --> 00:05:35,120
a filter.

87
00:05:35,780 --> 00:05:41,120
They're both good ideas as the context for the computation of our cash on hand component is important.

88
00:05:41,120 --> 00:05:46,580
And the key problem here, however, it's missing the concept of what the rows are and how to handle

89
00:05:46,580 --> 00:05:48,290
them in this table.

90
00:05:48,290 --> 00:05:53,720
We don't have a whole bunch of rows as we've aggregated them into just January in the computation for

91
00:05:53,720 --> 00:05:58,010
our cash on hand measure itself is based upon the context of this table.

92
00:05:58,010 --> 00:06:03,890
So it's calculated at January, which is where the computational comparison takes place.

93
00:06:04,700 --> 00:06:11,120
So simply filtering this in different ways to include more or less information subbed to that won't

94
00:06:11,120 --> 00:06:12,200
change our outcome.

95
00:06:12,560 --> 00:06:19,160
What we want to do is trigger this measure calculation at the sub level as opposed to our current context

96
00:06:19,160 --> 00:06:19,550
level.

97
00:06:20,270 --> 00:06:25,910
There's a whole series of functions that are based upon the primary function, such as some average

98
00:06:25,910 --> 00:06:27,800
in men with X on the end.

99
00:06:28,840 --> 00:06:32,800
Some ex average ex Kotex, Mendax and so on.

100
00:06:33,240 --> 00:06:37,710
We're going to use one of these, the same access to computers, interest expense correctly.

101
00:06:38,020 --> 00:06:43,660
Now these functions take a table first, and this is going to be the table that we want to evaluate

102
00:06:43,660 --> 00:06:49,540
our measure at in terms of context and then do the sum or the count or the average or whatever function

103
00:06:49,540 --> 00:06:50,860
it is you want across.

104
00:06:51,370 --> 00:06:57,250
Selecting the table is quite possibly the hardest part of figuring out what you need to do with respect

105
00:06:57,250 --> 00:07:04,000
to DACs in these scenarios, you need to think to yourself, what table do I want to sum across right

106
00:07:04,000 --> 00:07:04,420
now?

107
00:07:04,900 --> 00:07:09,960
The obvious answer might be that we're summing the interest expense, which is that our measures table.

108
00:07:10,090 --> 00:07:11,820
So that's what we want to sum across.

109
00:07:11,830 --> 00:07:12,160
Right.

110
00:07:12,910 --> 00:07:15,670
But that's not the context that we want to sum across.

111
00:07:15,670 --> 00:07:17,770
The context of the measures table is nothing.

112
00:07:18,040 --> 00:07:23,710
We want to calculate the interest expense for each day and then sum it up.

113
00:07:24,310 --> 00:07:28,270
So the context that we want is each row within our calendar table.

114
00:07:28,270 --> 00:07:31,940
So the calendar table will be the context for this function.

115
00:07:32,410 --> 00:07:38,260
So in this case, the table choice does turn out to be fairly simple, if not very obvious, since we're

116
00:07:38,260 --> 00:07:39,280
working with dates.

117
00:07:40,510 --> 00:07:47,410
You can try to visualize this whole process like this, the table that we're picking for this X function

118
00:07:48,070 --> 00:07:55,150
is where our external subcontracts will be applied for our computations with the filter applied to it

119
00:07:55,150 --> 00:07:56,980
from the context of our pivot table.

120
00:07:57,160 --> 00:08:02,680
So given that we're in January, which is the month we're looking at and we have this calendar table,

121
00:08:02,980 --> 00:08:08,860
the calendar table, some context, are these dates here, January one through January thirty one.

122
00:08:09,460 --> 00:08:15,070
Now, in our X function, we're going to take each one of these rows and we're going to calculate the

123
00:08:15,070 --> 00:08:23,590
measures result at each row prior to summing them together so we can see that this gives us the interest

124
00:08:23,590 --> 00:08:26,020
rate that we want to add up to our monthly total.

125
00:08:26,900 --> 00:08:34,010
I've written some X function with my F computation as the expression I press, OK, once I load this

126
00:08:34,010 --> 00:08:40,450
to the table, I can see at this month level that habré my month interest is non-zero.

127
00:08:40,730 --> 00:08:42,470
So we're accumulating interest.

128
00:08:42,500 --> 00:08:43,310
Is it correct?

129
00:08:43,700 --> 00:08:45,050
Well, let's drill down and see.

130
00:08:45,260 --> 00:08:51,230
Once I do this, we can see that the computations for each individual day work as well.

131
00:08:51,740 --> 00:08:54,730
At this point, you're getting pretty dangerous with DACs.

132
00:08:54,740 --> 00:08:56,720
You've conquered some X functions.

133
00:08:56,960 --> 00:09:01,700
You have basic measures down and you're starting to get a handle of context.

134
00:09:02,180 --> 00:09:07,640
In the next lesson, I'm going to introduce you to some technical helper functions for calculating some

135
00:09:07,640 --> 00:09:13,640
important values should be a short lesson covering things like how to handle the passage of time, calculate

136
00:09:13,640 --> 00:09:19,250
days, months, years, and then managing some basic computational errors that you'll likely come across

137
00:09:19,250 --> 00:09:19,970
at some point.