DAX Count consecutive days out of stock

[Dunbar]

Hi there,

I have data which keeps track of when certain items are out of stock. Now I also want to keep track of how many consecutive days a certain item has been out of stock for, excluding weekends and holidays when we are closed. See the format below what I would like it to be.

Code	Stock	Date	Days out of stock
200256453	0	16-2-2024	1
200349177	0	16-2-2024	1
200349178	0	16-2-2024	1
200255553	0	19-2-2024	1
200349177	0	19-2-2024	2
200349345	0	19-2-2024	1
200129152	0	20-2-2024	1
200349177	0	20-2-2024	3
200349178	0	20-2-2024	1
200595953	0	20-2-2024	2

I have no clue where to start. Anyone who can help me out?

[jolind1996]

Hi, Dunbar

ConsecutiveDaysOutOfStock = 
VAR CurrentDate = 'Table'[Date]
VAR CurrentCode = 'Table'[Code]
VAR PreviousDay = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALL('Table'),
            'Table'[Code] = CurrentCode && 
            'Table'[Date] < CurrentDate && 
            NOT('Table'[Date] IN Holidays[Date]) && 
            NOT(WEEKDAY('Table'[Date], 2) > 5)
        )
    )
VAR Result = 
    IF(
        CurrentDate - PreviousDay = 1,
        LOOKUPVALUE('Table'[Days out of stock], 'Table'[Date], PreviousDay, 'Table'[Code], CurrentCode) + 1,
        1
    )
RETURN
    Result

Please note that this assumes you have a separate ‘Holidays’ table that contains the dates of all holidays, in contrast to the above answer. If you don’t have this, you’ll need to create one.

This DAX expression calculates the difference between the current date and the previous date where the item was out of stock. If the difference is 1, it means the item has been out of stock for consecutive days, so it adds 1 to the previous ‘Days out of stock’. If the difference is not 1, it means the item has not been out of stock for consecutive days, so it resets the ‘Days out of stock’ to 1.

Please replace ‘Table’ with the name of your table, and ‘Holidays’ with the name of your holidays table. Also, replace ‘Code’, ‘Date’, and ‘Days out of stock’ with the names of your columns if they are different.

Best regards,

Johannes

[Dunbar]

If I comment out the Holidays part and just test the basic functionality, it shows 1 for every single item on every single day. What am I doing wrong?

[Dunbar]

Hi there Johannes. Thank you for the description, but I'm getting an error on this part

NOT('Table'[Date] IN Holidays[Date]) && 

I created the Holidays table but it says it's not a valid table. Adding quotation marks doesn't solve the issue. Any idea what I'm doing wrong?

[v-zhouwen-msft]

Hi @Dunbar ,

The table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure named ‘Days out of stock’

Days out of stock = COUNTROWS(FILTER('Tabelle1',WEEKDAY('Tabelle1'[Date],2) <= 5))

2. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dunbar]

Hi there, 

This doesn't quite answer my question. Your measure just counts the total days a certain item has been out of stock, but I'm looking for a way to tell me how many (work)days an item has been out of stock for in a row up to today. If the item was out of stock 4 days ago up to today, it should say 5 (4 previously + today). If the item was out of stock yesterday but today there's stock again, it should say 0.