DAX "Days in Stock" from Stock Movement table

Question

I am trying to count the number of "days in stock" for each product in the inventory. As a source of data, I have a dedicated Calendar table and a Stock Movement table containing records only for the days when movement actually occurred (not every day). There is a relationship between the tables.

With the code below I attempted to make a "left join" alternative in DAX and count the rows after. The result is meaningless.

I need to get the number of days where QTY > 0 from the beginning of the year including days with no movement. A "Calculate" is included for transferring the row context (Model, Branch,...) to the measure.

days in stock =
VAR StockPerDay =
    ADDCOLUMNS (
        'Calendar';
        "DailyQty"; CALCULATE (
            SUM ( StockMovement[qty] );
            StockMovement[Date] <= EARLIER ( 'Calendar'[Date] )
        )
    )
RETURN
    COUNTROWS ( FILTER ( StockPerDay; [DailyQty] > 0 ) )

The data in Stock Movement table looks like the following:

sample stock movement

I am new to DAX so any help will be much appreciated.

Can you give a sample of what your StockMovement table looks like? — Alexis Olson
@Alexis Olson just added the stock movement sample picture. Thanks — Albert SH

Albert SH Albert SH · Accepted Answer · 2019-07-01T21:03:59

I came up with a solution which hopefully works for me. Huge thanks to @Alexis Olson for editing my messy code, it made me take a fresh look.

DaysInStock :=
VAR StockPerDay =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Calendar';
            "DailyQty"; SUMX (
                FILTER ( StockMovement; StockMovement[Date] <= 'Calendar'[Date] );
                StockMovement[QTY]
            )
        );
        'Calendar'[Date]
            <= TODAY () - 1
    )
VAR NoOfDays =
    COUNTROWS ( FILTER ( StockPerDay; [DailyQty] > 0 ) )
VAR DateLimit =
    DATE ( YEAR ( TODAY () ); 1; 15 )
RETURN
    IF (
        NoOfDays >= DATEDIFF ( DateLimit; TODAY (); DAY );
        TODAY () - DateLimit;
        NoOfDays
    )

In case there is a way to optimize the above, please share your thoughts.

DAX "Days in Stock" from Stock Movement table

1 Answers