Periodic Snapshot with Missing Periods

[mweschenburg]

I have inventory fact table with periodic snapshots sliced by several dimensions.  Multiple organizations (Entity) upload inventory weekly/monthly (fiscal Date) and each monthly inventory can include more than one record (Growth Center).  Each entity may upload their inventory on different dates and sometimes whole periods are missed.

Screenshot #1:  Report model with the relevant Organization (Entity), Fiscal Date, and Growth Center dimensions.  The actual model includes additional dimensions, but they should behave similarly to Growth Center.

Screenshot #2:  Top visual shows the months (and specific dates) that each entity uploaded an inventory snapshot.  The dates look weird because they are based on our fiscal calendar.  Some months are missing from an entity, in which case I want to use the previous inventory snapshot for that entity.  The circled dates and arrows indicate what snapshot dates should be used.  For Entity=3340, I want to repeat the 20210731 inventory for August.  And for Entity=3586, I want to use the 20211030 inventory for November and December.

I started with a DAX sample from DAX Patterns site (https://www.daxpatterns.com/semi-additive-calculations/); using the “Closing Ever” measure.  I further simplified the DAX until I ended up with this:

Latest Prior Date =

VAR MaxDateReportContext = MAX('Fiscal Date'[Fiscal Date ID])

VAR MaxInventoryDatePerEntity =

GROUPBY(

FILTER('Inventory Balance', 'Inventory Balance'[Inventory Date ID] <= MaxDateReportContext),

'Inventory Balance'[Organization ID], "Inventory Date ID",

MAXX(CURRENTGROUP(), 'Inventory Balance'[Inventory Date ID])

)

RETURN MAXX(MaxInventoryDatePerEntity, [Inventory Date ID])

The bottom visual shows the inventory quantities and has the same monthly gaps. 

Latest Prior Quantity =

VAR MaxDateReportContext = MAX('Fiscal Date'[Fiscal Date ID])

VAR MaxInventoryDatePerEntity =

GROUPBY(

FILTER('Inventory Balance', 'Inventory Balance'[Inventory Date ID] <= MaxDateReportContext),

'Inventory Balance'[Entity], "Inventory Date ID",

MAXX(CURRENTGROUP(), 'Inventory Balance'[Inventory Date ID])

)

VAR FilterInventory =

TREATAS(MaxInventoryDatePerEntity, 'Inventory Balance'[Entity], 'Inventory Balance'[Inventory Date ID])

RETURN SUMX(FilterInventory, [Quantity On Hand])

These measures are not entirely wrong, but don’t fill in the missing months as expected.  I think I need to tell the measures to ignore the context filters from the month columns in the matrix, but not sure how to do that.  I tried many variations of ALL(), ALLEXCEPT(), LASTNONBLANK() but really not sure where they need to be inserted.

Screenshot #3:  My desired result, which curiously uses the exact same DAX measures.  I got it to work by disabling the relationship between Inventory Balance and Fiscal Date, which sort of confirms my suspicion about why the results in Screenshot #3 don’t work.

Can someone suggest how to disable/ignore the Fiscal Date relationship in the DAX measure instead of in the report model?

PBIX file is available here:  https://www.dropbox.com/s/hwu5p02ts1m1cf5/Inventory.pbix?dl=0

Screenshot #1

Screenshot #2

Screenshot #3

[mweschenburg]

Unfortunately not the solution.  Using the revised measure with ALL(), the monthly quantity matrix looks like...

[amitchandak]

@mweschenburg , Try with all

Latest Prior Quantity =

VAR MaxDateReportContext = MAX('Fiscal Date'[Fiscal Date ID])

VAR MaxInventoryDatePerEntity =

GROUPBY(

FILTER(all('Inventory Balance'), 'Inventory Balance'[Inventory Date ID] <= MaxDateReportContext),

'Inventory Balance'[Entity], "Inventory Date ID",

MAXX(CURRENTGROUP(), 'Inventory Balance'[Inventory Date ID])

)

VAR FilterInventory =

TREATAS(MaxInventoryDatePerEntity, 'Inventory Balance'[Entity], 'Inventory Balance'[Inventory Date ID])

RETURN SUMX(FilterInventory, [Quantity On Hand])