In the last lesson, I introduced you to measures and how to write some basic ones with a brief short
introduction to more advanced measures, of course, in writing that advanced measure, we wrote a fairly
long formula involving a sum that we already had, plus some other filter adjustments to our data.
So let's go back and look at it and begin breaking it down.
First, the calculate function will be a very, very common friend and ally as it's used with measures
to modify and adjust context for many different needs.
In this case, we've created a custom measure for our calculation, the sum of sales, but what if we
wanted something more complicated than just the sum of our sales here?
Or what if our sales measure itself needed to change?
For some reason, with the way our current measures are handled, we would have to adjust both our sales
total cumulative and our sales total measures, which would be a hassle and would be prone to errors.
We can be smart about this and change our measures together.
As our first example, I can simply replace this some sales sales with the sales total measure.
Since it is the sum of the sales, this bill simplifies our current formula so that we don't have a
formula within a formula and it structures our cumulative calculation to include any adjustments that
we make to our sales.
Total calculation function.
Suppose, for example, that I go to my sales total and I change it from something sales to something
sales times to now both my sales total and my sales total, cumulative computation stay in sync without
me having to update the cumulative calculations separately.
This is pretty handy, but it doesn't always work quite so conveniently.
Let's go back to this cumulative measure and take a look at it again.
We have what appears to be another measure in our max calendar date function area.
This clearly looks like another opportunity to have a measure called Maximum Date Calculated and then
use it here.
Let's go create that max tape measure, shall we?
I'll call it maximum date.
Make take the maximum of the date and we'll apply it when I add it into our cumulative function and
close out, what do we see?
Well, our cumulative function no longer works or what's happened.
As it turns out, you can't use predefined measures when performing a comparison like this in a filter
function.
We need to use the formula computation.
The technical reasons elude me.
However, my general rule of thumb is to use the specific calculation rather than a separate measure
whenever applying a filter through this formula approach.
Now, outside of applying a filter, it's a very good practice to use compound measures when possible
to keep them in sync, for example, of that we might not really be interested in the cumulative sales
or purchase orders or wages, but we would be very interested in the cumulative running cash status
of the business, which would be the cumulative profit at any point in time.
To calculate this, we could calculate the cumulative of all three of our other fields, or we could
just use the profit field that we've already created.
I've created this measure already.
I've called it cash on hand and we can go take a look.
It's very simple, it's the same pattern as we used in our sales total, but instead of using the sales
total, we've used the profit measure.
The profit measure crosses multiple tables, but the context is the same here that we need.
So let's hop out to this pivot table and shuffle some fields around.
I'm going to drop the quarter in exchange for the date hierarchy and I'm going to add cash on hand.
Now, at this level doesn't look special since we have our obvious profit and cash on hand totals for
all of twenty nineteen.
But let's expand twenty nineteen to months and then I'll expand January two days.
Within this profit measure, which is a very common tax pattern, we've created a pretty useful output
that lets us figure out the first point in time in which we break even on cash flow.
We can see that Albertsons and Kroger's are cash flow positive by January.
Twenty three twenty nineteen.
But this isn't far enough.
Let's enhance this a little bit more.
Let's suppose that the annual rate the company can borrow cash at is twenty five percent with simple
interest.
Basically the interest doesn't compound so I can calculate the interest at it in any given day.
Using cash on hand.
Ten zero point twenty five divided by three hundred sixty five at least with my cash on hand is negative
so I can create a new measure pretty simply like that.
Right.
New measure.
Interest expense is the name use a formula equals if cash on hand less than or equal to zero cash on
hand times zero point twenty five divided by three sixty five comma zero.
Easy.
So let's add that to the pivot table and voila it's there.
We have our interest in each day.
But let's take a step back.
We're using cash on hand in a comparison here.
I didn't think we could do that.
Well, what do you think happens when we condense our table and we look at the month and whole?
We obviously have interest expenses in January.
We see them here.
It'll just sum up the total, right?
Well, why is our total here zero then?
That's obviously incorrect.
Our measure computations calculate at the contexts aggregate level.
So the aggregate level here where it does if cash on hand is less than equal, zero sees the cash on
hand is positive.
So it gives us a zero, therefore no interest expenses.
You might think something like maybe adding a sum to our measure would help or using calculate and applying
a filter.
They're both good ideas as the context for the computation of our cash on hand component is important.
And the key problem here, however, it's missing the concept of what the rows are and how to handle
them in this table.
We don't have a whole bunch of rows as we've aggregated them into just January in the computation for
our cash on hand measure itself is based upon the context of this table.
So it's calculated at January, which is where the computational comparison takes place.
So simply filtering this in different ways to include more or less information subbed to that won't
change our outcome.
What we want to do is trigger this measure calculation at the sub level as opposed to our current context
level.
There's a whole series of functions that are based upon the primary function, such as some average
in men with X on the end.
Some ex average ex Kotex, Mendax and so on.
We're going to use one of these, the same access to computers, interest expense correctly.
Now these functions take a table first, and this is going to be the table that we want to evaluate
our measure at in terms of context and then do the sum or the count or the average or whatever function
it is you want across.
Selecting the table is quite possibly the hardest part of figuring out what you need to do with respect
to DACs in these scenarios, you need to think to yourself, what table do I want to sum across right
now?
The obvious answer might be that we're summing the interest expense, which is that our measures table.
So that's what we want to sum across.
Right.
But that's not the context that we want to sum across.
The context of the measures table is nothing.
We want to calculate the interest expense for each day and then sum it up.
So the context that we want is each row within our calendar table.
So the calendar table will be the context for this function.
So in this case, the table choice does turn out to be fairly simple, if not very obvious, since we're
working with dates.
You can try to visualize this whole process like this, the table that we're picking for this X function
is where our external subcontracts will be applied for our computations with the filter applied to it
from the context of our pivot table.
So given that we're in January, which is the month we're looking at and we have this calendar table,
the calendar table, some context, are these dates here, January one through January thirty one.
Now, in our X function, we're going to take each one of these rows and we're going to calculate the
measures result at each row prior to summing them together so we can see that this gives us the interest
rate that we want to add up to our monthly total.
I've written some X function with my F computation as the expression I press, OK, once I load this
to the table, I can see at this month level that habré my month interest is non-zero.
So we're accumulating interest.
Is it correct?
Well, let's drill down and see.
Once I do this, we can see that the computations for each individual day work as well.
At this point, you're getting pretty dangerous with DACs.
You've conquered some X functions.
You have basic measures down and you're starting to get a handle of context.
In the next lesson, I'm going to introduce you to some technical helper functions for calculating some
important values should be a short lesson covering things like how to handle the passage of time, calculate
days, months, years, and then managing some basic computational errors that you'll likely come across
at some point.