number of days zero stocks

[jayanthan]

Hi All

I wanted to identify number of days stockbalance has been zero,In the below example stockbalance has been zero from 16th Oct 2017 to 13th Jan 2018(89 days) for a specfic product in a given location

ProductId	LocationID	Custom_Date	StockBalance	stocktransaction
545199839852	5542	10/16/2017	0	0
545199839852	5542	1/13/2018	1	1
545199839852	5542	4/12/2018	8174	8173
545199839852	4228	5/14/2018	84	84
545199839852	4381	5/15/2018	8	8
545199839852	4554	5/28/2018	684	684
545199839852	4283	5/31/2018	2	2
545199839852	4344	6/11/2018	15	15
545199839852	4622	6/12/2018	34	34
545199839852	4381	6/21/2018	6	-2
545199839852	4622	7/8/2018	31	-3
545199839852	4381	7/11/2018	4	-2
545199839852	5542	7/15/2018	211	-7963
545199839852	4381	7/15/2018	2	-2
545199839852	4622	7/15/2018	30	-1
545199839852	4622	8/4/2018	29	-1
545199839852	4622	8/23/2018	27	-2
545199839852	5039	8/30/2018	1	1
545199839852	4622	10/1/2018	25	-2
545199839852	4344	10/6/2018	9	-6
545199839852	4554	10/10/2018	689	5
545199839852	4622	10/10/2018	0	-25
545199839852	5027	10/10/2018	5	5

Jayanthan

[v-cherch-msft · Accepted solution]

Hi @jayanthan 

You may add an index column in query editor first and then create below measures.

NextDay =
IF (
    MAX ( Table1[StockBalance] ) = 0,
    CALCULATE (
        MAX ( Table1[Custom_Date] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) + 1 )
    )
)

Date diff = DATEDIFF(MAX(Table1[Custom_Date]),[NextDay],DAY)

Regards,

Cherie

[jayanthan]

Hi v-cherch-msft

This is awesome, thanks for the support

Regards

Jayanthan