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 ,

You can use the following formula in Power BI Desktop to show the total budget (end result of cumulative) - the yearly expenditures:

Total Budget = LASTNONBLANK('Table'[Budget],1) - LASTNONBLANK('Table'[Expenditures],1)

This formula will return the difference between the last non-blank value in the 'Budget' column and the last non-blank value in the 'Expenditures' column.

If the above is not what you want, please tell me what your raw data is and what your expected result will look like. It is best if one table is the original table and one table is the table of the desired result.

Best Regards,

Stephen Tao

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