Calculate difference of budget between prior periods

[Anonymous]

Hello I just started using Power Query and Power BI.

I am trying to visualize the budget and realisation of a cost center over time. However in the data source (Excel), the values of budget and realisation are not yet useable.

• Budget is the budget of the corresponding month for the whole year. As you can see the (year)budget can change every month.
• Realisation is a cumulative total, based on the realisation in the previous month(s) and that of the current month.

When I try to visualize in Power BI the budget and realisation of a cost center over time. The budget is summed and therefore not very usable. Also I can't easy present the net realisation for a given month.

Sample data

Cost center	Budget	Realisation	Date
10001	89	15	31-1-2024
10001	125	36	29-2-2024
10001	132	48	31-3-2024
10001	132	67	30-4-2024
10001	115	88	31-5-2024
10002	45	30	31-1-2024
10002	165	52	29-2-2024

Therefore I want to transform the data in Power Query, so that the values of budget and realisation only present the net value over a given period and can be used in Power BI. 

Desired data

Cost center	Budget	Realisation	Date
10001	89	15	31-1-2024
10001	36	21	29-2-2024
10001	7	12	31-3-2024
10001	0	19	30-4-2024
10001	-17	21	31-5-2024
10002	45	30	31-1-2024
10002	120	22	29-2-2024

There are many questions and solutions about DAX or M with calculating the difference between same period last year or previous period. However in those questions the data that is used is different and therefore I can't find a similar topic. If somebody can help me towards the correct direction to solve this problem I am very grateful.

[ThxAlot]

A proper use-case for incorporating 2 most advanced features of PBI, namely Window function and Calculation Group,

[Anonymous]

Thanks for this solution. I will need to read more about those features and how to use them properly.

[AlienSx]

M

let
    Source = your_table,
    rows = List.Buffer(Table.ToRecords(Source)),
    gen = List.Generate(
        () => [i = 0, r = rows{0}, upd = r],
        (x) => x[i] < List.Count(rows),
        (x) => 
            [i = x[i] + 1, 
            r = rows{i}, 
            upd = r & 
                (if r[Cost center] <> x[r][Cost center] 
                then []
                else [Budget = r[Budget] - x[r][Budget], 
                    Realisation = r[Realisation] - x[r][Realisation]]
                )
            ],
        (x) => x[upd]
    ),
    z = Table.FromRecords(gen)
in
    z

[v-jingzhan-msft · Accepted solution]

If you are new to Power BI, creating two new columns with DAX method will be much easier. In your original table, you can create two new columns with below formula. Replace 'Table' with your actual table name. 

New Realisation = 
var previousValue = MAXX(FILTER('Table','Table'[Cost center]=EARLIER('Table'[Cost center])&&'Table'[Date]=EOMONTH(EARLIER('Table'[Date]),-1)),'Table'[Realisation])
return
'Table'[Realisation] - previousValue

New Budget = 
var previousBudget = MAXX(FILTER('Table','Table'[Cost center]=EARLIER('Table'[Cost center])&&'Table'[Date]=EOMONTH(EARLIER('Table'[Date]),-1)),'Table'[Budget])
return
'Table'[Budget] - previousBudget

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Anonymous]

Dear Jing,

How can I change the formula, to prevent pulling December's values for January. As January is the first month of the year and thus should not deduct the previous month (December).

Kind regards,

Patrick  

[v-jingzhan-msft]

Please try this. Use an IF statement. 

New Realisation =
var previousValue = MAXX(FILTER('Table','Table'[Cost center]=EARLIER('Table'[Cost center])&&'Table'[Date]=EOMONTH(EARLIER('Table'[Date]),-1)),'Table'[Realisation])
return    
IF(MONTH('Table'[Date])=1,'Table'[Realisation],'Table'[Realisation] - previousValue)