Working with Inventory, Balancesheet and Headcount Type Data (Semi-additive measures and LASTNONBLANKVALUE() Power BI DAX Tutorial)

Some things are just not that easy to calculate. Let us take headcount for example, if I had ten people in my department in January, February, and March, then my Q1 headcount is not thirty it is still ten. So with sales we can just sum all the data up, but with things like headcount, inventory or balancesheet, we simply do not have this luxury. Typically what we do for those metrics is a last known value calculation, so whatever is the last record given the time period that we are trying to analyze, that’s the record that we will use in our calculation. If my period is Q1, then the only record that I care about is the latest record in Q1 for which I have any data.

We call this behavior semi-additive because we cannot add things up like we normally would across time periods that roll up into our quarter (if that’s the grain of our analysis) but we can add things up across let’s say stores and products. It would take a few pages two express this definition more eloquently, but that is what the YouTube is for. In this video I go through some definitions for semi-additive measures and then I cover a good way to implement an Inventory calculation that would very well work for headcount and balancesheet type scenarios using the LASTNONBLANKVALUE() function.

You can download the tutorial materials here.

Last Known Inventory = 
var maxKnownDate = MAX('Date'[Date])
var lastKnownInventory =
CALCULATETABLE(
    SUMMARIZE(
        Inventory, 
        Inventory[Store], 
        Inventory[Product],
        "LastKnownInventory",
        LASTNONBLANKVALUE(
            'Date'[Date], 
            SUM(Inventory[Qty])
        )
    ),
    FILTER(
        ALL('Date'), 
        'Date'[Date]<=maxKnownDate
    )
)
return SUMX(lastKnownInventory, [LastKnownInventory])