how can we produce a snapshot table using powerbi measure?

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

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top