How to produce a snapshot table using Power BI measure

Question

My intention is to populate days of the month to simulate a data warehouse periodic snapshot table using DAX measures. My goal is to show non-additive values for the quantity.

Consider the following transactions:

The granularity of my snapshot table is day. So it should show the following:

Take note that a day may have multiple entries but I am only interested in the latest entry for the day. If I am looking at the figures using a week period it should show the latest entry for the week. It all depends on the context fixter.

However after applying the measure I end up with:

There are three transactions. Two on day 2 and the other on day 4. Instead of calculating a running total I want to show the latest Qty for the days which have no transactions without running accumulating totals. So, day 4 should show 4 instead of summing up day 3 and day 4 which gives me 10. I've been experimenting with LASTNONBLANK without much success.

This is the measure I'm using:

Snapshot = 
CALCULATE(
    SUM('Inventory'[Quantity]),
    FILTER(
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX( 'Date'[Date] )
    )
)

There are two tables involved:

Table # 1: Inventory table containing the transactions. It includes the product id, the date/time the transaction was recorded and the quantity.

Table # 2: A date table 'Date' which has been marked as a date table in Power BI. There is a relationship between the Inventory and the Date table based on a date key. So, in the measure, 'Date'[Date] refers to the Date column in the Date table.

sergiom sergiom · Accepted Answer · 2020-11-07T08:30:20

You can use the LASTNONBLANKVALUE function, that returns the last value of the expression specified as second parameter sorted by the column specified as first parameter.

Since LASTNONBLANKVALUE implicitly wraps the second parameter into a CALCULATE, a context transition happens and therefore the row context is transformed into the corresponding filter context. So we also need to use VALUES to apply the filter context to the T[Qty] column. The returned table is a single row column and DAX can automatically convert a single column, single row table to a scalar value.

Then, since we don't have a dimension table we have to get rid of cross-filtering, therefore we must use REMOVEFILTERS over the whole table.

the filter expression T[Day] < MaxDay is needed because LASTNONBLANKVALUE must be called in a filter context containing all the rows preceding and including the current one.

So, assuming that the table name is T with fields Day and Qty like in your sample data, this code should work

Edit: changed in order to support multiple rows with same day, assuming the desired result is the sum of the last day quantities

Measure =
VAR MaxDay =
    MAX ( T[Day] )
RETURN
    CALCULATE (
        LASTNONBLANKVALUE (
            T[Day],
            SUM ( T[Qty] )
        ),
        T[Day] <= MaxDay,
        REMOVEFILTERS ( T )
    ) + 0

Edit: after reading the comments, this might work on your model (untested)

Measure =
VAR MaxDay =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        LASTNONBLANKVALUE (
            Inventory[RecordedDate],
            SUM ( Inventory[Quantity] )
        ),
        'Date'[Date] <= MaxDay
    ) + 0

How to produce a snapshot table using Power BI measure

1 Answers