Forum Discussion
Advocate IIIRunning totals backwards
Dear community friends
I am curious about how to make measure for running totals backwards - from today back into the past.
I have Today inventory, at the end of the day (900),
Also have income end outcome of the inventory for each day (also for this last day - Today)
Question is how to make measure that will calculate inventory for each day backwards !?
| Inventory at the begining of Today 01.03.2024 was =1000 | |||||||
| # | Date | IN | OUT | IN-OUT | Inventory | ||
| 1 | 01.03.2024 | 100 | 200 | -100 | 900 | at the end of the day (Today) | |
| 2 | 29.02.2024 | 10 | 20 | -10 | 890 | ||
| 3 | 28.02.2024 | 300 | 100 | 200 | 1090 | ||
| 4 | 27.02.2024 | 200 | 200 | 0 | 1090 | ||
| 5 | 26.02.2024 | 100 | 50 | 50 | 1140 | ||
| 6 | 25.02.2024 | 10 | 200 | -190 | 950 | ||
| 7 | 24.02.2024 | 50 | 60 | -10 | 940 | ||
| 9 | 23.02.2024 | 100 | 50 | 50 | 990 | ||
| 10 | 22.02.2024 | 100 | 40 | 60 | 1050 | ||
| 11 | 21.02.2024 | 30 | 0 | 30 | 1080 | ||
| 12 | 20.02.2024 | 0 | 50 | -50 | 1030 | ||
| 13 | 19.02.2024 | 20 | 10 | 10 | 1040 | ||
| ... | ... | ... | ... | ... | ... |
Ice cream is free by me.
Thanks a lot for your effort.
Hi gvlado ,
As Nithinr said, it is possible to do what you need to do based on this DAX code, here are my thoughts and additions.
First create a table.
A slight change to Nithinr's DAX code will give you the results you need.
MEASURE = VAR _A = CALCULATE ( SUM ( 'Table'[IN-OUT] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] >= MIN ( 'Table'[Date] ) ) ) VAR _AA = _A + 900 RETURN _AABest Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Small correction 900 -> 1000
And types of columns must be defined
---- BOMeasure----------
Measure =VAR _A = CALCULATE(SUM('Table'[IN-OUT]),FILTER(ALL('Table'),'Table'[Date] >= MIN('Table'[Date])))VAR _AA = _A + 1000RETURN_AA--- EOMEasure---
9 Replies
NithinrResolver III
Is this the one you are looking for
Reverse Cumulative Total = CALCULATE( SUM('Table'[Inventory]), FILTER( ALL('Table'), 'Table'[Date] >= MIN('Table'[Date]) ) )Hi gvlado ,
As Nithinr said, it is possible to do what you need to do based on this DAX code, here are my thoughts and additions.
First create a table.
A slight change to Nithinr's DAX code will give you the results you need.
MEASURE = VAR _A = CALCULATE ( SUM ( 'Table'[IN-OUT] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] >= MIN ( 'Table'[Date] ) ) ) VAR _AA = _A + 900 RETURN _AABest Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- gvladoSmall correction 900 -> 1000
And types of columns must be defined
---- BOMeasure----------
Measure =VAR _A = CALCULATE(SUM('Table'[IN-OUT]),FILTER(ALL('Table'),'Table'[Date] >= MIN('Table'[Date])))VAR _AA = _A + 1000RETURN_AA--- EOMEasure--- - gvlado
how did you get 940?
Should go like it is on ilustration
Inventory = VAR PreviousRowValue = CALCULATE( SUM('Table'[IN-OUT]) + 1000, FILTER( 'Table', 'Table'[Date] > EARLIER('Table'[Date]) ) ) RETURN IF(ISBLANK(PreviousRowValue), 'Table'[IN-OUT], PreviousRowValue + 'Table'[IN-OUT])- Ashish_Mathur
Super User
Hi,
For closing inventory on 1/3/2024 to be 900, the closing inventory on 29/2/2024 should be 1000. Why is it 890?
- gvlado
It is because receiving (IN=10) and deliveiring (OUT=20) -> means that this day was inventory smaller for 10 (-10). So, I have in and out for every day. Only for last day (1/3/2024) there is data fon inventory at the end of the day. It means calculation of inventory starts from 1000.
- Ashish_Mathur
Hi,
I have solved a similar problem in the attached PBI files. Please review them and adapt them to your setup.
- gvlado
It should go like ilustration on the excell
Starting inventory for beginning of the day is separated table. IN-OUT is change for each day (it is 2nd table)
After loading both table I have Inventory and Change of inventory by day backwards (1000 - 100=900, then 900 - 10=890, then 890+200=1090, ....etc). The question is how measure for Inventory at the end of the day should look like.
