Hi, I have a dataset just as below :ART_IdDateStock Qty_StockLast Day Stock <> 0Nb of days130/04/202030003000 124/06/2020-52995 116/09/2020-12994 128/09/2020-502944 106/10/2020-502894 119/10/2020-52889 121/10/2020-1022787 113/11/2020-302757 107/12/2020-4602297 114/12/2020-10001297 112/01/202101297 109/03/2021-501247 111/03/2021-600647 128/05/202130003647 108/06/2021-1003547 105/07/2021-1003447 116/07/2021-503397 128/10/2021-5002897 110/12/2021-5002397 114/12/20212502647 129/12/2021-2647014/12/202115 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

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

Frequent Visitor

3 years ago

Solved

Running total compare to same date last year

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

Need Help

v-yadongf-msft

3 years ago

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.

Running total compare to same date last year.pbix24 KB

6 Replies

amitchandak
Super User
3 years ago
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
)
)
MarieAmell
Frequent Visitor
3 years ago
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
Ashish_Mathur
Super User
3 years ago
Hi,

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

MarieAmell

Frequent Visitor

3 years ago

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,

v-yadongf-msft
Community Support
3 years ago
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.
Running total compare to same date last year.pbix24 KB
MarieAmell
Frequent Visitor
3 years ago
Sorry for my very late reply, I was connected with a former account and missed the last messages. Thanks a lot for your help

Forum Discussion

Running total compare to same date last year

6 Replies

Related content

Running Totals

Running total

Waterfall Charts with running totals

SumX? - cumulative running total

Tableau to PowerBI Running Totals Issue