Reply
avatar user
Anonymous
Not applicable

Calculate difference of budget between prior periods

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 centerBudgetRealisationDate
10001891531-1-2024
100011253629-2-2024
100011324831-3-2024
100011326730-4-2024
100011158831-5-2024
10002453031-1-2024
100021655229-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 centerBudgetRealisationDate
10001891531-1-2024
10001362129-2-2024
1000171231-3-2024
1000101930-4-2024
10001-172131-5-2024
10002453031-1-2024
100021202229-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.

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

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

vjingzhanmsft_0-1718344640913.png

 

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

View solution in original post

6 REPLIES 6
ThxAlot
Super User
Super User

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

ThxAlot_0-1718482074046.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



avatar user
Anonymous
Not applicable

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

AlienSx
Super User
Super User

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
Community Support
Community Support

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

vjingzhanmsft_0-1718344640913.png

 

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

avatar user
Anonymous
Not applicable

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  

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)

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)