Running totals backwards

[gvlado]

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.

[v-yilong-msft · Accepted solution]

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
    _AA

Best Regards

Yilong Zhou

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

[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.

[Ashish_Mathur]

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.

[v-yilong-msft · Accepted solution]

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
    _AA

Best Regards

Yilong Zhou

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

[gvlado · Accepted solution]

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 + 1000

RETURN

_AA

--- EOMEasure---

[gvlado]

how did you get 940?
Should go like it is on ilustration

[Nithinr]

Is this the one you are looking for

Reverse Cumulative Total = 
CALCULATE(
    SUM('Table'[Inventory]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= MIN('Table'[Date])
    )
)