Help on reflecting Actual/Forecast Data and number of Months in DAX for Financial Report

DAX DAX Calculations
1

Context
I have prepared a financial report which comprises the Profit or Loss Statement (“P/L”) for my company (data has been replaced with dummy data for confidentiality purposes).

This financial report was prepared with the methodologies I learned from the Financial Reporting with Power BI course by Sam McKay.

The P/L is prepared in a matrix where I have a template in Excel to determine P/L row items in a sequential order. The numbers are then reflected using DAX.

There are 4 filters, i.e. Dates (Year and Month), Country, Customer Channel (Online or Offline), Data Type (Actual or Forecast).

Problem
Problem #1: My data consists of Actuals (historical financial data) and Forecast (full year forecast which are prepared every quarter, hence there will be 4 sets of Forecast data prepared every quarter, i.e. Forecast Q1, Forecast Q2, Forecast Q3, Forecast Q4.

In my DAX, I currently manually write this filter, i.e. whether I want the calculation to be using Actual data or Forecast data. However, this becomes tedious when I have to update the Forecast, e.g. from Forecast 1 to Forecast 2.

The reason why I have to do this is because in my P/L report page, I have multiple matrix tables to show multiple Data Types (Actual, Forecast 1, Forecast 2 etc). Otherwise, if I were to put the Data Type filter in the page, all my data will change when I click the filter, e.g. if I click Forecast Q1, my Forecast column in my matrix table will also become Actual data.

How do I make it such that I can reflect these with ease in my measure without having to manually change the Forecast data type?

You may refer to measures in the P/L Summary Measure group.

Problem #2: In the P/L Summary Measure group, I have a measure called R/O. In this calculation, it is a division as follows (Month of Apr as example):

((Latest Forecast - Actual YTD) / months remaining in the year ) / (Actual YTD) / current months)
= ((Latest full year forecast data - Actual Jan - Apr data) / 8 ) / Actual Jan - Apr data / 4)

After every month end, I have to manually update the numbers 8 and 4. For example, for May’s financial report, I will have to edit 8 to 7 and 4 to 5 in my measure.

How do I make it such that the manual keying in can be “automated” in my measure?

Financial Report.pbix (271.4 KB)
Financial Report.xlsx (237.3 KB)

2

Bumping this post for more visibility from our experts and users.

3

Hi @pacocha.florence - Don’t think we can make it totally dynamic as there needs to be a way to define which value to take. For your questions, I have come up with below solutions. Do let me know if it doesn’t suits your requirement.

Problem 1 - Have created a separate table called Forecast and added values Forecast 1 and Forecast 2. Created a new measure as shown below that will return [Forecast 1] and [Forecast 2] based on selection. Now to apply filter either

  1. We can add the new filter in the Visual filters or
  2. Use a Slicer and using Edit interactions decide which visual needs to get updated. I will prefer second option as you can control multiple visual based on this along with Display.
Forecast Val_Ankit = var SelFcst = SELECTEDVALUE(Forecast[Forecast])
return 
SWITCH(SelFcst,"Forecast 1",[Forecast 1],[Forecast 2])

For Forecast display on visual. used it in Card visual and overlap with measure name in Matrix visual.

Forecast Display_Ankit = var SelFcst = SELECTEDVALUE(Forecast[Forecast])
return 
SWITCH(SelFcst,"Forecast 1","Forecast 1","Forecast 2")

Problem #2 - Again have a created a separate table called Division and added Month/Val1/Val2 columns in it. Now based on selection of the Month, Val1/Val2 will get selected and applied on Measure. Check measure R/O_Ankit.

Check “Profit or Loss_Ankit” page in attached PBIX where I have used above measures and Filters.

Financial Report.pbix (283.1 KB)

Thanks
Ankit J

4

Hello @pacocha.florence

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

5

Hello @pacocha.florence

We notice that no response was received from you on the post above.

We’d like to know if you need further help with this inquiry. If so, can you let us know where you’re stuck and what additional assistance you need?

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

Thank you!

6

Hello, just had time to log back in. I’ll try the suggested solution by @ankit in the next couple days and let you know if I have further questions, thank you very much for your help

7

Hi @pacocha.florence,

How was it? was it able to solve your inquiry?

If it does, please do not forget to mark the reply as the SOLUTION to your inquiry.

If not, please let us know where you’re stuck and what additional assistance you need.

thank you!

8

Hi @pacocha.florence

Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.