We can encounter some weird effects when using Stock Measures in Power BI. Let’s look at how such a situation can emerge and how to solve it.
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.
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.