Showing stock on all calendar dates (including those with no fact rows) (PBIX included)

[Titatovenaar2]

Hello everyone,

I’m trying to display a daily “start stock” measure on every date in my table—even if there’s no corresponding DateKey row in the Fact table. The idea is that for each date, I want to see the last known “begin stock” plus all movements from that anchor date up to the current date.

In the printscreen below you can see how my measure has blanks for datekeys it has no info for in the main fact table 'Facts'.

Model Setup

• A star schema with a single-direction relationship from 'DIM Calendar' to 'Facts'.

• 'DIM FactTypering' is a separate dimension that has a relationship to Facts, used to filter [Category3] = "Begin Stock". Meaning stocks per ProductionPeriod have been pre-calculated in SQL Server.

• [SUM Material Movement] is a measure that sums stock movements, stored in the same Fact table.

Goal: On every date in the table (or basically every date in the Calendar), I want to calculate:

1. The most recent (<= current date) “begin stock” date (call it the anchor).

2. The pre-calculated stock on that anchor date (stored in the Fact table).

3. The sum of all movements from the day after the anchor date up to (and including) the current row date.

4. Display this on the current date row, even if the Fact table has no row for that date.

What I’ve Tried

I’ve been using a DAX pattern like this:

MB Start Stock = 
VAR ThisDate =
    MAX('DIM Calendar'[Date]) 
VAR LastAnchorDate =
    CALCULATE(
        MAX('DIM Calendar'[Date]),
        ALL('DIM Calendar'),                         
        'DIM Calendar'[Date] <= ThisDate,                     
        'DIM FactTypering'[Category3] = "Begin Stock" 
    )
VAR StockAtAnchor =
    CALCULATE(
        SUM(Facts[Value]),
        KEEPFILTERS('DIM FactTypering'[Category3] = "Begin Stock"),
        'DIM Calendar'[Date] = LastAnchorDate
    )
VAR MovementsSinceAnchor =
    CALCULATE(
        [SUM Material Movement],
        FILTER(
            ALL('DIM Calendar'),
            'DIM Calendar'[Date] > LastAnchorDate &&
            'DIM Calendar'[Date] <= ThisDate
        )
    )
RETURN
IF(
    ISBLANK(LastAnchorDate),
    BLANK(),
    StockAtAnchor + MovementsSinceAnchor
)

I’d appreciate any suggestions, tips, or code examples that might help get this measure working for all calendar dates. Thank you in advance!

[TomMartens]

 Hey @Titatovenaar2 ,

please provide a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link here in the thread.
If you did not use manual input for the sample data, also share the spreadsheet. This is important because otherwise there is no chance to adapted the semantic model.

Do not forget to describe the expected result based on your sample data.

Regards,
Tom

[Titatovenaar2]

PBIX included! 🙂

[Titatovenaar2]

I made the .pbix with some very simplistic sample data, but it reveals the problem I have perfectly.

StocksTest.pbix 

Below how I would like the [MB Start Stock] to behave: each record have a start stock, even if there is no available date in the Facts table.

I do not want to have the start stock available on Every record for the calendar, since dates that have no movement data or not a period start or end date are not interesting to show the value (since these dates would have no changes).

And for some reason I always have these NULL records, which makes sense, but I would like to add and subtract the movements to the stocks on available dates in the table.

Kind regards, Igor