Running total compare to same date last year

[MarieAmell]

Hi,

I have a dataset just as below :

ART_Id	Date	Stock Qty	_Stock	Last Day Stock <> 0	Nb of days
1	30/04/2020	3000	3000
1	24/06/2020	-5	2995
1	16/09/2020	-1	2994
1	28/09/2020	-50	2944
1	06/10/2020	-50	2894
1	19/10/2020	-5	2889
1	21/10/2020	-102	2787
1	13/11/2020	-30	2757
1	07/12/2020	-460	2297
1	14/12/2020	-1000	1297
1	12/01/2021	0	1297
1	09/03/2021	-50	1247
1	11/03/2021	-600	647
1	28/05/2021	3000	3647
1	08/06/2021	-100	3547
1	05/07/2021	-100	3447
1	16/07/2021	-50	3397
1	28/10/2021	-500	2897
1	10/12/2021	-500	2397
1	14/12/2021	250	2647
1	29/12/2021	-2647	0	14/12/2021	15

• The "_Stock" column is a measure, to have the running total : 

_Stock =

CALCULATE(

    SUM(Inventory[Stock Qty])

    , FILTER(ALL('Time'[Date]),

    'Time'[Date]<=MAX('Time'[Date])

    )
)

• I'd like to know which articles have a stock = 0 today, so with this measure, I can select the ones with _Stock = 0. And For these ones, I'd like to have the last day the stock was different than 0, and the number of days between them.

I've created the following measure, but it doesn't work (it returns the date just as in the 2nd column) :

_LastStockDate = CALCULATE(MAX('Time'[Date]), FILTER(Inventory, [_Stock]<>0))

_NbdaysStock0 = DATEDIFF([_LastStockDate], [Today], DAY)

• With this, I'd like to be able to tell they have x articles with zero stock today, with y number of days without any stock, and compare to the same day last year.

Also, I have a lot of lines in my data set, so if you have any tips to make these calculations easier and faster, it would be very helpful.

Thanks a lot in advance for your help,

Kind regards,

Marie

[MarieAmell]

Sorry for my very late reply, I was connected with a former account and missed the last messages. Thanks a lot for your help

[v-yadongf-msft · Accepted solution]

Hi @MarieAmell ,

This is my test table:

Please try following DAX to crete new columns:

Stock =
CALCULATE (
    SUM ( 'Table'[Stock Qty] ),
    FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
)

Last Day Stock <> 0 =
IF (
    'Table'[Stock] = 0,
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
    )
)

Nb of days =
DATEDIFF ( 'Table'[Last Day Stock <> 0], 'Table'[Date], DAY )

You will get the table like this:

The result you want:

Best regards,

Yadong Fang

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

[MarieAmell]

Sorry my request wasn't clear.

I have the following dataset, with quantity in and out each day, for several articles (Here, just one articles).

ART_Id	Date	Stock Qty
1	30/04/2020	3000
1	24/06/2020	-5
1	16/09/2020	-1
1	28/09/2020	-50
1	06/10/2020	-50
1	19/10/2020	-5
1	21/10/2020	-102
1	13/11/2020	-30
1	07/12/2020	-460
1	14/12/2020	-1000
1	12/01/2021	0
1	09/03/2021	-50
1	11/03/2021	-600
1	28/05/2021	3000
1	08/06/2021	-100
1	05/07/2021	-100
1	16/07/2021	-50
1	28/10/2021	-500
1	10/12/2021	-500
1	14/12/2021	250
1	29/12/2021	-2647
1	30/12/2021	2397
1	21/02/2022	-500
1	25/02/2022	-100
1	18/03/2022	-100
1	06/04/2022	-50
1	28/04/2022	-1000
1	19/05/2022	-150
1	03/06/2022	-100
1	16/06/2022	-200
1	24/06/2022	-197

I need the following calculation :

Art_Id	Date	Running stock	Last date with stock > 0	Nb of days with stock = 0
1	29/12/2021	0	14/12/2021	15
1	24/06/2022	0	16/06/2022	8

The running stock doesn't have a starting date (or when it's at 0; it can start from this point).

The last day with stock > 0 is the day with movments on the stock pre"vious to the date when it's at 0.

And finally, I have the difference between the dates.

At the end, I need to have a 

TODAY
Art_Id	Date	Running stock	Last date with stock > 0	Nb of days with stock = 0
3	16/11/2022	0	10/11/2022	6
5	16/11/2022	0	09/11/2022	7
Total
2 articles out of stock today	13

SPLY
Art_Id	Date	Running stock	Last date with stock > 0	Nb of days with stock = 0
1	16/11/2021	0	08/11/2021	6
2	16/11/2021	0	30/10/2021	7
6	16/11/2021	0	15/11/2021	1
Total
3 articles out of stock SDLY				14

I hope it's clearer,

Thanks a lot for your help,

Kind regards,

[Ashish_Mathur]

Hi,

Your expected result is not clear.  Please show that clearly.

[MarieAmell]

Thank you @amitchandak , it should be good to calculate the stock SPLY.

My first issue is to find the last date where my stock was not at 0 : in my example above, the stock for this article is at 0 the 29/12, and was not at 0 the 14/12 (No movement between). So I'd like to find the 14/12 (And then calculate the number of days Difference between the two dates).

Thank you again for your time.

Best regards

[amitchandak]

@MarieAmell , try like

_Stock =
var _max1= MAX('Time'[Date])
var _max = date(year(_max1) -1, month(_max1), Day(_max1))
return
CALCULATE(
SUM(Inventory[Stock Qty])
, FILTER(ALL('Time'[Date]),
'Time'[Date]<=_max
)
)