Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I've got one table with Budget and one with Acutals and have made up a P&L statement. What's the easiest way to substitute actuals with budget values when the actuals are missing to have a full year P&L statement before the end of the year?
Thanks!
Solved! Go to Solution.
Hi @sparcraft73 ,
You could create a measure to calculate the result.
Measure =
VAR a =
CALCULATE ( SUM ( Actuals[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Period] ) )
RETURN
IF ( ISBLANK ( a ), SELECTEDVALUE ( Budget[Amount] ), a )
Then replace your oringinal Actual column with this measure in the matrix.
Can you show us a screenshot of your tables and their relationships ?
Do you have a Calendar table that is linked to both the budget table and the actuals table.
If so you can link to a record in budget and a matching record in actuals and create a calculated colum in the actuals table that just substitutes the related value from the budget table when actuals is missing.
Help when you know. Ask when you don't!
This is my records and relationships:
Accounts
Actuals
Budget
Relationships
Report matrix where I want the missing Actuals to be substituted by the budget values for each month/account:
How do you think I can do this?
The easiest way would be to send me a small sample version in a power bi file... or an excel spread sheet with one sheet for each table, that I can import into power bi
I will work out the code and post it as an answer
Help when you know. Ask when you don't!
or you could just paste the sample data into your reply, instead of using images
Help when you know. Ask when you don't!
Here's the sample data:
Actuals
Date | Account | Amount |
2019-01-04 | 3000 | 144 |
2019-01-07 | 3000 | 214 |
2019-01-10 | 4000 | -60 |
2019-02-09 | 4000 | -37 |
2019-02-16 | 4000 | -123 |
2019-02-18 | 3000 | 101 |
Budget:
Period | Account | Amount |
2019-01 | 3000 | 127 |
2019-02 | 3000 | 75 |
2019-03 | 3000 | 218 |
2019-04 | 3000 | 22 |
2019-05 | 3000 | 195 |
2019-06 | 3000 | 268 |
2019-07 | 3000 | 275 |
2019-08 | 3000 | 237 |
2019-09 | 3000 | 122 |
2019-10 | 3000 | 281 |
2019-11 | 3000 | 159 |
2019-12 | 3000 | 220 |
2019-01 | 4000 | -179 |
2019-02 | 4000 | -138 |
2019-03 | 4000 | -295 |
2019-04 | 4000 | -197 |
2019-05 | 4000 | -177 |
2019-06 | 4000 | -219 |
2019-07 | 4000 | -261 |
2019-08 | 4000 | -292 |
2019-09 | 4000 | -50 |
2019-10 | 4000 | -170 |
2019-11 | 4000 | -258 |
2019-12 | 4000 | -286 |
Accounts
AcID | Name |
3000 | Sales |
4000 | Cost |
Hi @sparcraft73 ,
You could create a measure to calculate the result.
Measure =
VAR a =
CALCULATE ( SUM ( Actuals[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Period] ) )
RETURN
IF ( ISBLANK ( a ), SELECTEDVALUE ( Budget[Amount] ), a )
Then replace your oringinal Actual column with this measure in the matrix.
One issue though is that the row sum doesn't seem to work!
As you can see the ActualFY works but the row sum just summaraize like the Actual.
Great! Thank's a lot!!
This made my day!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |