Dax to find the OOS (Out of Stock) for consecutive Days

[Anonymous]

Hi Team,

Please find the following data.here how to find the Out of Stock for consecutive days.Here you can see Item 100 only having OOS for 3 consecutive days.So i need to get the answer 3.

Date	Item	OOS
10-06-2019	100	1
11-06-2019	150
12-06-2019	100	1
13-06-2019	100	1
14-06-2019	101
15-06-2019	102
16-06-2019	100
17-06-2019	102	1
18-06-2019	102
19-06-2019	102	1

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a rank column.Then create below measures to get the count.

Rank = RANKX(FILTER(Stock,Stock[Item]=EARLIER(Stock[Item])),Stock[Date],,ASC)

Next_oos = CALCULATE(MAX(Stock[OOS]),FILTER(ALL(Stock),Stock[Item]=MAX(Stock[Item])&&Stock[Rank]=MAX(Stock[Rank])+1))

Last_oos = CALCULATE(MAX(Stock[OOS]),FILTER(ALL(Stock),Stock[Item]=MAX(Stock[Item])&&Stock[Rank]=MAX(Stock[Rank])-1))

Count = COUNTROWS(FILTER(Stock,Stock[OOS]=1&&OR([Next_oos]=1,[Last_oos]=1)))

Regards,

[Anonymous]

Hi @v-cherch-msft 

Thank you for the reply 🙂 Its really help full.

One more thing,can you suggest me to visualise the following requirement.

I need to showcase how many items are OOS for 1 Day,2 Day 3 day etc

Days	1D	2D	3D
Item Count	1	2	0