Cumulative value - show total - expenditures

[ChloeDL]

Good mornign, 

We currently have a table showing the cumulative budgets per year but we would like to represent the data in a way that shows the total budget (end result of cumulative) - the yearl expenditures. See table below. 

Fiscal Year	Budget	Expenditures	Free balance
2022-2023	200	50	150
2023-2024	150	50	100
2024-2025	100	50	50
2025-2026	50	50	0
2026-2027	0	50	-50

What formula would help us achieve that?

[Anonymous · Accepted solution]

Hi  @ChloeDL ,

Here are the steps you can follow：

1. Create calculated column.

number =
VALUE(LEFT('Table'[Fiscal Year ],4))

Expenditures =
var _current=
SUMX(

    FILTER(ALL('Table'),'Table'[number]=EARLIER('Table'[number])),[Budget ])
var _next=
SUMX(
    FILTER(ALL('Table'),'Table'[number]=EARLIER('Table'[number])+1),[Budget ])
var _result=
_current - _next
return
IF(
    [number]=MAXX(ALL('Table'),[number]),
    (SUMX(
    FILTER(ALL('Table'),'Table'[number]=EARLIER('Table'[number])-2),[Budget ])
    -
    SUMX(
    FILTER(ALL('Table'),'Table'[number]=EARLIER('Table'[number])-1),[Budget ]))
    -
    _current
,_result)

Free balance =
[Budget ] - [Expenditures]

total = 
SUMX(
    ALL('Table'),[Free balance])

2. Result:

Best Regards,

Liu Yang

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