How to produce a snapshot table using Power BI DAX measure

[Anonymous]

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 as shown below:

However after applying the measure I end up with:

There are two transactions. One on day 2 and the other on day 4. Instead of calculating a running total I want to show the latest Qty in the Snapshot column for the days which have no transactions. So, day 4 should show 4 instead of summing up day 3 and day 4 which gives me 6.

This is the measure I'm using:

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

[daxer-almighty · Accepted solution]

In all earnest, such calculations should be performed in Power Query, especially on big fact tables. Doing this through DAX is not only slow. It's highly inefficient because of poor compression.

[Anonymous]

You are absolutely right. Just with sample data already it doesn't perform well. I will try the Power Query path. Would you have a reference of this being done using Power Query?

[DataInsights]

@Anonymous,

Try this measure:

Snapshot = 
VAR vCurrentDay =
    MAX ( SnapshotTest[Day] )
VAR vMaxDayWithQty =
    CALCULATE (
        MAX ( SnapshotTest[Day] ),
        ALL ( SnapshotTest ),
        SnapshotTest[Day] <= vCurrentDay,
        NOT ISBLANK ( SnapshotTest[Qty] )
    )
VAR vResult =
    CALCULATE (
        SUM ( SnapshotTest[Qty] ),
        ALL ( SnapshotTest ),
        SnapshotTest[Day] = vMaxDayWithQty
    )
RETURN
    IF ( ISBLANK ( vResult ), 0, vResult )

[Anonymous]

I tried you're solution using my tables but it didn't work. I'm using a marked date table and hopefully that still can work with your recommended solution. I only had blanks for the Snapshot column:

Quantity Snapshot =
VAR vCurrentDay =
MAX ( 'Date'[Day] )
VAR vMaxDayWithQty =
CALCULATE (
MAX ( 'Date'[Day] ),
ALL ( 'Date' ),
'Date'[Day] <= vCurrentDay,
NOT ISBLANK ( 'Inventory'[Quantity] )
)
VAR vResult =
CALCULATE (
SUM ( 'Inventory'[Quantity] ),
ALL ( 'Inventory' ),
'Date'[Day] = vMaxDayWithQty
)
RETURN
IF ( ISBLANK ( vResult ), 0, vResult )

[Anonymous]

Much appreciated for your reply. I have a couple of questions:
1. Is SnapshotTest a Date table? In my scenario I have a Date table marked as date table in Power BI and an Inventory table.
2. It is also possible to have multiple entries per day. In this case I would be interested only in the last entry for the day. The inventory table has another column called RecordedDate which shows data and time. Rather than summing up how would you select the Qty related to the latest transaction for the day?