Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
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)


)



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!

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)

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors