Count zero stock days for selected period

[Petanek333]

Hi,

I have a table like this:

To make it simple I only have one product. In the third column there is a number of products left for each day. If there are more rows with the same date, the table is chronologically sorted by column Index. 

If the date is missing, there was no stock movement and stock left value is the same as the last record. For example on 11.9.2022 the stock left value is 0 as the last stock value for the last record was the fourth record of day 9.9.2022. 

I need a measure (not using a calculated column) that returns number of days with zero stock for the selected period. 

If there were some non zero stock values for particular day with multiple records such as 9.9.2022 then this day cannot be a zero stock day (for example 9.9.2022 is NOT a zero stock day even though the day ended with zero stock).

If I select a period from 1.9.2022 to 30.9.2022, the number of zero stock days for Product A should be 18. 

The logic of the calculation is that a zero day is when the product has only one record in a day and that day has stock left = 0, also a zero stock day is a day that has no record at all and the last record before that day was stock left =0.
If a day has multiple records and at least one of them had a stock left greater than 0, then it is not a zero stock day, likewise if the record of the day is missing and the last record of the previous day was greater than 0.

Sample data here: Sample file 

[Petanek333 · Accepted solution]

I think I solved it 🙂

I added an Index column in Query Editor and reused a code made by @PaulDBrown  - thank you very much again 🙂

This is the code (I named it Index by Paul because I use it regulary and it is great)

Index by Paul = 
VAR _MinSel =
    MIN ( 'Calendar table'[Date] )
VAR _MXIndex =
    CALCULATE (
        MAX ( Data[Index Paul] ),
        FILTER ( ALL ( 'Calendar table'[Date] ), 'Calendar table'[Date] <= _MinSel ),
        ALLEXCEPT (
            Data,
            Data[ID]
        )
    )
RETURN
    CALCULATE (
        [Stock left],
        FILTER ( ALL ( Data ), Data[Index Paul] = _MXIndex )
    )

Then I followed your logic @MAwwad , so big thanks to you too.

This is the final code that is working just as expected:

Zero Stock Days =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER (
                    'Calendar table',
                    AND (
                        'Calendar table'[Date] >= MIN ( 'Calendar table'[Date] ),
                        'Calendar table'[Date] <= MAX ( 'Calendar table'[Date] )
                    )
                ),
                'Calendar table'[Date],
                "Any Stock", MAX ( Data[Stock left] ),
                "Helper field", [Index by Paul]
            ),
            "Final count",
                SWITCH (
                    TRUE (),
                    [Any Stock] > 0, 1,
                    ISBLANK ( [Any Stock] )
                        && [Helper field] > 0, 1,
                    [Helper field]
                )
        ),
        [Final count] = 0
    )
)

[MAwwad]

Try this

And dont forget to do necessary changes 

Zero Stock Days =
COUNTROWS(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
FILTER(Table,
AND(
Table[Date] >= MIN(Table[Date]),
Table[Date] <= MAX(Table[Date])
)
),
Table[Date], "Stock", MAX(Table[Stock Left])
),
"Zero Stock Day",
SWITCH(
TRUE(),
[Stock] = 0 && COUNTX(FILTER(Table, Table[Date] = EARLIER([Date])), Table[Stock Left]) = 1, 1,
[Stock] = 0 && COUNTX(FILTER(Table, Table[Date] = EARLIER([Date])), Table[Stock Left]) = 0, 1,
0
)
),
[Zero Stock Day] = 1
)
)

[Petanek333]

Hi @MAwwad , thank you for your help. Unfortunately I can't get it to work. 

Firstly - that will most probably be my mistake, but I don't know how to "do necessary changes" in order to use Calendar[Date] and Data[Date] correctly. The virtual table only shows the Dates that have some record and does not show the dates like 10.9.2022 that do not have a record but are considered a zero stock day. SO it returns 9 instead of 18.

Secondly - I don't know if it would count correctly as this value - "Stock", MAX(Table[Stock Left]) - for the date 9.9.2022 is 5 in a virtual table and I need to sort by Index column, so that even though the date 9.9.2022 is not a zero stock day, the day after it which has no record should be a zero stock day because 9.9.2022 ends with 0 stock. It might need something like this I think to be incorporated into the measure, don't you think? To correctly output that the day ended with no stock and the day after it should be a zero stock day.

Last stock value of a day = 
var latest = MAX(Data[Index])
return
CALCULATE(
    SUM(Data[Stock left]),Data[Index] = latest)

EDIT: I think I managed to do the necessary changes but one thing does not work correctly. Days 15.9.2022 and 16.9.2022 are considered zero stock days because there is no record but in fact there is no change of stock and the last day with a record was 14.9.2022 which has stock of 2. But this might be the only incorrect thing in the measure.