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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sparcraft73
Frequent Visitor

How to use Budget values as Actual values for the rest of the year

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!

 

 

1 ACCEPTED 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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

9 REPLIES 9
kentyler
Solution Sage
Solution Sage

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


This is my records and relationships:

 

Accounts

AcountAcount

Actuals

ActualsActuals

Budget

BudgetBudget

 

DateTblDateTbl

Relationships

RelationshipsRelationships

 

Report matrix where I want the missing Actuals to be substituted by the budget values for each month/account:

 

report.JPG

 
 

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


or you could just paste the sample data into your reply, instead of using images





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Here's the sample data:

 

Actuals

DateAccountAmount
2019-01-043000144
2019-01-073000214
2019-01-104000-60
2019-02-094000-37
2019-02-164000-123
2019-02-183000101

 

Budget:

PeriodAccountAmount
2019-013000127
2019-02300075
2019-033000218
2019-04300022
2019-053000195
2019-063000268
2019-073000275
2019-083000237
2019-093000122
2019-103000281
2019-113000159
2019-123000220
2019-014000-179
2019-024000-138
2019-034000-295
2019-044000-197
2019-054000-177
2019-064000-219
2019-074000-261
2019-084000-292
2019-094000-50
2019-104000-170
2019-114000-258
2019-124000-286

 

Accounts

AcIDName
3000Sales
4000Cost

 

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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.

 

error.JPG

Great! Thank's a lot!!

 

This made my day!

 
Is there any way the substituted values could be cursive?
 
 
 
 
 

Here's a link to the PBIX:

 

https://justbeamit.com/vta7x

 

Thank's for your helpfulness!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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