Forum Discussion
Running total compare to same date last year
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.
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,