How to include budget data in the income statement template shown in the financial reporting module?

Power BI
1

I recently completed the Financial Reporting course and have been working through various other EDNA modules for budgeting and forecasting (Budgeting & Forecasting mini-series, Business Analytics mini-series) and am now able to compare my budget to actual tables correctly using the budget allocation method outlined in the Budgeting and Forecating mini-series.

However, when I try to use my budget measures with the income statement template from the Financial Reporting course, the allocations do not correctly align w/ the dates in my template table.

Below is the “Selected Month Actual” formula from the Financial Reporting as well my modified “Selected Month Budget” formula that is not working correctly. Everything else related to my budget table is working correctly so I think its just me not understanding how the template works with the data. I’ve also included two of the measures used within the “Selected Month Budget” measure so that you can see how the measures within the Switch(True() section are set up.

Please let me know if you have any questions.

This works correctly, it is from the Financial Reporting Course

Selected Month Actuals_2 =
VAR CurrentItem = SELECTEDVALUE(‘Template IS’[Items (Normalized)])
Return
Switch(True(),
CurrentItem = “Total Operating Revenue”, DIVIDE([Revenues_2],1,0),
CurrentItem = “Total Operating Expenses”, DIVIDE([Operating Expenses_2],1,0),
CurrentItem = “Total Cost of Goods Sold”, DIVIDE([COGS_2],1,0),
CurrentItem = “Gross Margin”, DIVIDE([Gross Margin_2],1,0),
CurrentItem = “Net Operating Income”, DIVIDE([Net Operating Income_2],1,0),
CurrentItem = “Income/(Loss) after Technology & Financing”, DIVIDE([Net Income after TF_2],1,0),
CurrentItem = “Income/(Loss) after Govt Stimulus”, DIVIDE([Net Income after Govt_2],1,0),
CALCULATE([Actuals_2 (000)],
Filter(‘IS Actuals’,‘IS Actuals’[Subcategory]=CurrentItem)))

Using the dax from above, I created this formula which is not working

Selected Month Budget =

VAR CurrentItem = SELECTEDVALUE(‘Template IS’[Items (Normalized)])
Return
Switch(True(),
CurrentItem = “Total Operating Revenue”, DIVIDE([Budget Revenues],1,0),
CurrentItem = “Total Operating Expenses”, DIVIDE([Budget Operating Expenses],1,0),
CurrentItem = “Total Cost of Goods Sold”, DIVIDE([Budget COGS],1,0),
CurrentItem = “Gross Margin”, DIVIDE([Budget Gross Margin],1,0),
CurrentItem = “Net Operating Income”, DIVIDE([Budget Net Operating Income],1,0),
CurrentItem = “Income/(Loss) after Technology & Financing”, DIVIDE([Budget Net Income after TF],1,0),
CurrentItem = “Income/(Loss) after Govt Stimulus”, DIVIDE([Budget Net Income after Govt],1,0),
CALCULATE([Budget (000)],
Filter(‘IS Budget’,‘IS Budget’[Subcategory]=CurrentItem)))

Here are the measures within the switch(true above

Budget Revenues =
CALCULATE([Budget Allocation], ‘IS Budget’[Type] = “Revenues”)

Budget Allocation =
([Days in Date Context]/[Days in Year])*[Budget Income]

Budget Income =
Sum(‘IS Budget’[Amount])

2

Hi @Sdowney, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hi @Sdowney, we’ve noticed that no response has been received from you since August 3.

We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, or any other supporting links and details.

This will help other users to assist you in your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Hi @Sdowney, we’ve noticed that no response has been received from

We waited for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. Due to inactivity, we’ll be tagging this post as Solved.