cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sparcraft73 Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

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

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 Established Member
Established Member

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

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.

sparcraft73 Frequent Visitor
Frequent Visitor

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

This is my records and relationships:

 

Accounts

accounts.JPGAcount

Actuals

actuals.JPGActuals

Budget

budget.JPGBudget

 

datetbl.JPGDateTbl

Relationships

relationships.JPGRelationships

 

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?

kentyler Established Member
Established Member

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

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

kentyler Established Member
Established Member

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

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

sparcraft73 Frequent Visitor
Frequent Visitor

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

Here's a link to the PBIX:

 

https://justbeamit.com/vta7x

 

Thank's for your helpfulness!!

sparcraft73 Frequent Visitor
Frequent Visitor

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

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

 

Community Support Team
Community Support Team

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

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

sparcraft73 Frequent Visitor
Frequent Visitor

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

Great! Thank's a lot!!

 

This made my day!

 
Is there any way the substituted values could be cursive?
 
 
 
 
 
sparcraft73 Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)