Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
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.
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!
Thanks for this solution. I will need to read more about those features and how to use them properly.
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
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!
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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
13 | |
12 |