DAX measure to calculate the number of discrete times an item was stocked out over a period of time

[RettT]

Hi,

I'm looking for help with writing a measure that will calculate the number of times an item went out of stock during a time period. Table example is below. The expected result for this would be 3 discrete stock outs. 

Thanks!

Date	Stock out	Comment
1/1/2023	0	In stock
1/2/2023	1	Out of stock
1/3/2023	0	In stock
1/4/2023	1	Out of stock
1/5/2023	1	Out of stock
1/6/2023	0	In stock
1/7/2023	1	Out of stock
1/8/2023	1	Out of stock
1/9/2023	1	Out of stock
1/10/2023	0	In stock
1/11/2023	0	In stock

[barritown · Accepted solution]

Hi @RettT,

I'd add an auxiliary column with a flag showing that the status of the column "Stock out" changed from 0 to 1. Then you could sum this column to obtain the necessary value.

Below you can find this solution packed into a measure. I used a slightly updated dataset as you can see.

The very same measure in plain text:

Measure = 
VAR _ExtData = 
    ADDCOLUMNS ( Data,
    "@Trigger",
    VAR CurrentItem = [Item]
    VAR CurrentDate = [Date]
    VAR PreviousDate = MAXX ( FILTER ( Data, [Item] = CurrentItem && [Date] < CurrentDate ), [Date] )
    VAR PreviousValue = MINX ( FILTER ( Data, [Item] = CurrentItem && [Date] = PreviousDate ), [Stock out] ) + 0
    RETURN IF ( [Stock out] - PreviousValue = 1, 1, 0 ) )
RETURN SUMX ( _ExtData, [@Trigger] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown · Accepted solution]

Hi @RettT,

I'd add an auxiliary column with a flag showing that the status of the column "Stock out" changed from 0 to 1. Then you could sum this column to obtain the necessary value.

Below you can find this solution packed into a measure. I used a slightly updated dataset as you can see.

The very same measure in plain text:

Measure = 
VAR _ExtData = 
    ADDCOLUMNS ( Data,
    "@Trigger",
    VAR CurrentItem = [Item]
    VAR CurrentDate = [Date]
    VAR PreviousDate = MAXX ( FILTER ( Data, [Item] = CurrentItem && [Date] < CurrentDate ), [Date] )
    VAR PreviousValue = MINX ( FILTER ( Data, [Item] = CurrentItem && [Date] = PreviousDate ), [Stock out] ) + 0
    RETURN IF ( [Stock out] - PreviousValue = 1, 1, 0 ) )
RETURN SUMX ( _ExtData, [@Trigger] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[RettT · Accepted solution]

This worked great and will be very helpful. I very much appreciate your time and efforts!