Quick Trick: Average of the last values

I want to share one quick and easy trick that warehouse logistics analysts should appreciate. Working as an analyst and consultant, I came across a similar problem several times. And recently, in our Power BI Telegram chat, the following question arose: You need to calculate the average value of the balances in the warehouses (or some other nonadditive / seven-additive metric), and for each month you need to take only the last available value.

Actually, there are enough solutions on the Internet, and here’s one more for you 🙂

And if you are too lazy to read, you can download the ready-made solution here.

Suppose there is a data model with such a fact table

We assume that [Value] is the balance of goods in stock at a specific date. And, as we see, it is not recorded every day (it’s wild to see this in our age of high technology, but this situation is quite common, unfortunately). Based on the conditions of the initial request, for each month we need to take data for the last available day, and take the average from this bunch of values.

Let’s find the last value:

LastNonBlankDateValue = 

    LASTNONBLANKVALUE(
        Dates[Date],
        SUM(Stock[Value])
    )

Translating the formula into human language: Give me the sum of [Value] ​​for the last date of the selected period for which this sum exists. Easy, right? 🙂

We’ll throw a field with a month and a measure on the canvas, plus a slicer by date:

Great! As you can see, our measure takes strictly the last line for each month. If suddenly we had several values ​​for the last date – the measure would simply summarize them.

Now all that remains for us is to summarize what we see in the table and divide by the number of selected months in the slicer.

However, here I want to take note. Please, select on the slicer the period from 1 Jan 2019 to 1 Nov 2019 inclusive.
In fact, we have selected 11 periods, but in the table, we see only 10 – since on the first day of November there is no data in the fact table.

The job of BI developer is not just coding and painting diagrams. It is also communication with the customer about how to correctly calculate one or another indicator. The question of 10 or 11 periods is not a question about DAX, but a question of business logic and an approved methodology. Always keep this in mind, especially when working with complex metrics, and feel free to ask “stupid” questions to the customer.

Let’s go back to the desktop. Depending on what we decided with the periods, our formula with their count can look like this:

SelectedPeriodsCount = 
COUNTROWS(VALUES(Dates[Period]))

SelectedPeriodsCountAlt = 
CALCULATE(
    COUNTROWS(VALUES(Dates[Period])),
    Stock
)

In the first case, we rely on the slicer only; in the second, we additionally look at the availability of data in the fact table. Accordingly, we get 11 and 10.

And now we need to summarize the last values ​​for each month and divide by the measure just created:

ResultMeasure = 
var summ =
SUMX(
    VALUES(Dates[Period]),
    [LastNonBlankDateValue]
)
var mcount =
[SelectedPeriodsCount]

return
DIVIDE(
    summ,
    mcount
)


ResultMeasureAlt = 
var summ =
SUMX(
    VALUES(Dates[Period]),
    [LastNonBlankDateValue]
)
var mcount =
[SelectedPeriodsCountAlt]

return
DIVIDE(
    summ,
    mcount
)

We throw it all on the canvas, and get something like this simple report:

If you need to take the latest data not for a month, but, for example, for a week or a quarter, nothing prevents you from creating the corresponding columns in the date table and replacing ‘Dates'[Period] in the appropriate places. Just try!