Introduction
Stock calculations are commonly used when data cannot be aggregated over time.
For example, summing up my bank account balance over time would be a bad idea. It would be a good idea for me, but not my bank.
Currently, I am working on a client project to create a reporting solution for Human Resources data.
One key figure is the Headcount, which is also a stock measure, as we have stored the Headcount over time for each month in a Fact table.
This looks like an easy task.
But there can be scenarios where we must go one extra step to get the correct results.
Now, let’s dive into it.
Base Stock Measure
Firstly, the correct name for Stock Measure is Semi-Additive Measures. This is because, as mentioned above, they will not aggregate data over time but over all other Dimensions.
The Base Semi-Additive Measure for calculating the Headcount is simple:
Headcount =
VAR LastDataDay = LASTNONBLANK('Date'[Date]
,[Headcount (Base)])
RETURN
CALCULATE([Headcount (Base)]
,LastDataDay
)
The Measure [Headcount (Base)] contains the simple Aggregation (SUM) to get the needed result.
I always work with Base Measures to be able to include additional logic, which can be reused in the subsequent Measures. One example is using a scaling factor when the user wants to see thousands or millions without needing the automatic Display units in Power BI, which adds weird scaling factors, like T for thousands or M for millions.
Then, I use the LASTNONBLANK() function to get the last date with a result from the Headcount (Base) Measure. The result of this function is then applied as a filter in the CALCULATE() call to get the final result.
You can find an exhaustive explanation of this solution on DAX Patterns. The article is linked in the References section below. There, you can find the link to another article I wrote when I encountered other scenarios with Semi-additive Measures.
Here is the result for the Headcount of one Manager within our fictive organization hierarchy:
As you can see, the Headcount changes between January and February and April and May.
Nevertheless, the Total shows the last known Headcount, which is ten.
So far, so good.
Weird results
In my situation, each employee is assigned to a Group.
When I use the same Measure while looking at the distribution of the Employees between these groups, I get this:
At first glance, it looks fine.
But when I sum the values for each row, I get eleven.
Why does this happen?
You will find the answer when you think about the Filter Context per row. Think about it for a minute before continuing.
.
.
.
.
The reason is that we look at the whole year.
Look at the following diagram, which shows the development of the Headcount by Group over time:
When we look at each month, we can see that one employee in Group 1 left the company at the End of April. Thus, he will count as a Headcount until then.
But we got a new Employee in Group 4 starting in February.
By the end of the year, the team still had ten members. This is the view when we look only at the Headcount by the End of the Year.
The Base Measure gets ten in the Total because the Measure has no knowledge (No Filter applied) by Group.
But when calculating the Result by Group, the last known result for "Group 1" is one by April. That is shown in the Result by Group when we look at the entire year.
I can prove this with a slight change in the Measure:
As you can see in the first row, the last date with a Result from the Headcount (Base) Measure is 30.04.2022. This is the last date where there is a Result from the Headcount (Base) Measure when looking for "Group 1".
Thus, the one member of Group 1 is listed in the Result.
As with many other cases, this is a matter of definition:
- When we look at the entire year, we see that, at one point during the year, we had one Employee in Group 1.
- When we want to look only at the last day of the year, we shouldn’t get a result for Group 1.
If the first definition is correct, we should hide the Total line, as it will confuse the users.
If the second definition is correct, we should change the Measure to show only the Headcount by the end of the year.
Going the next step
To implement the second definition, I defined a new Measure with the following logic:
- Which is the last Date in the current Filter context?
- Which is the last Date with Data in the Fact table?
- If the Last Date of the current Filter context contains no data (Point 2 < Point 1), then show the last known Data from the Fact Table.
- If the Last Date of the current Filter context contains data (Point 2 >= Point 1), show the Result for the last Date in the current Filter context.
This is the Measure I came up with:
Headcount (By End of Period) =
VAR LastActiveDate = LASTDATE('Date'[Date])
VAR LastDateKeyWithData = CALCULATE(MAX('FactOrg'[DateKey])
,REMOVEFILTERS('FactOrg')
)
VAR LastDateWithData = LOOKUPVALUE('Date'[Date]
,'Date'[DateKey]
,LastDateKeyWithData
)
RETURN
IF (LastDateWithData < LastActiveDate
,CALCULATE([Headcount (Base)]
,REMOVEFILTERS('Date')
,'Date'[DateKey] = LastDateKeyWithData)
,CALCULATE([Headcount (Base)]
,LastActiveDate
)
)
I must add the check to the Measure to avoid an empty result when looking at a year with no Data for the 31. December.
This Measure returns the following result:
As expected, Group 1 has no Headcount when looking at the whole year.
But it returns the correct result when looking at each month:
This measure works correctly for any period, like quarters, semesters, or weeks.
Caveats
This Measure works only when we know the data is mapped to the last day of each period.
If data is mapped to other dates, this Measure will return wrong results.
The same applies when we have mixed data, where some data points are mapped to the last date of each period, and some other data points aren’t.
I explained such a situation in my article about Semi-Additive Measures linked below.
So, it all depends on the definition of the needed result and which solution is correct.
Conclusion
Knowing the definition of the Result is vital to avoiding situations where the result is not logical and the user can interpret the results as wrong.
This is the worst situation possible, as it can severely impact the acceptance of a product.
We noticed this "issue" during testing and decided to use the above-mentioned solution.
But we will have further discussions about whether this solution is really the correct one.
Usually, nobody thinks about these situations until we have them. So, don’t worry when you have them the next time you build a report with Semi-Additive Measures. But remember to test such cases as well.
And this is another example of when it’s vital to understand all implications of how the Filter context is applied.
This understanding is critical when trying to understand the result of a Dax Measure.
However, explaining such a result to stakeholders who do not understand this technical concept is challenging.
Usually, they want both in all cases: The correct result by row and the right result in the Total.
In such cases, I must go one more step further to solve such requirements. But I always have mixed feelings when adding more and more complex logic to my Measures.
I am reluctant because it will be difficult for anyone else to understand why I did it in such a complicated way. This adds unnecessary effort to the maintenance of the solution. Sometimes, I even experienced a situation where my client removed the complex logic, as nobody could understand it.
I try to persuade my stakeholders to go for a compromise to get the best acceptable result without such complex and very custom logic.
References
The data is self-generated with phantasy names.
I generated this complete list by multiplying a list of First- and Last-Names with each other.
I generated the Fact table by multiplying the list of Employees with the list of the last Dates for each month over three years (CROSS JOIN) from my date table.
Semi-additive calculations on DAX-Patterns:
My first article about solving specific challenges with semi-additive Measures:
You can support my work, which I do during my free time, through
https://buymeacoffee.com/salvatorecagliari
Or scan this QR Code:
Any support is greatly appreciated and helps me find more time to create more content for you.
Thank you a lot.