- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for this solution. I will need to read more about those features and how to use them properly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
06-05-2024 05:40 PM | |||
10-05-2023 06:32 PM | |||
01-14-2024 03:23 PM | |||
09-04-2024 01:43 PM | |||
09-29-2024 01:51 PM |
User | Count |
---|---|
27 | |
25 | |
25 | |
13 | |
9 |
User | Count |
---|---|
24 | |
19 | |
16 | |
13 | |
10 |