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
_Adrian
Frequent Visitor

How to prevent double-counting of old forecast months that have since become actuals

Here's my scenario:

It's September and I have the following financial data in my model:
- Actuals (up to and including August)
- Forecast 9: forecast data for September onwards
- Forecast 7: an older forecast from July, which has the then 'forecast' data for July onwards

Actuals and Forecast 9 splice together perfectly and give a continuous monthly profile.

However, if I want to show Actuals and Forecast 7, both data sets include data for July and August. I want to see the picture as it was then, so need to prevent the Actuals for July and August from displaying.

It gets more complicated as I have many more historical forecasts (Forecast 6, 5, 4, etc.), but the above is the essence of the problem. Also, the actuals are more granular than the forecasts, so they currently sit in separate fact tables.

How is best to prevent the double-counting of old forecast months that have since become actuals too? These double-counted months would be different for each old forecast.


Any help or suggestions would be appreciated.

Thanks,

Adrian

3 REPLIES 3
Greg_Deckler
Super User
Super User

@_Adrian - So, making some assumptions about things, maybe try:

Measure = 
  VAR __Date = MAX('Calendar'[Date])
  VAR __ActualsMax = MAXX(ALL('Actuals'),[Date])
RETURN
  IF(
    __Date > __ActualsMax,
    SUMX(__Forecast,[Value]),
    SUMX(__Actuals,[Value])
  )

Honestly though I have no idea what you are trying to do, create a new table, or what. The concept above assumes a seperate date table, you figure out if the date is more or less than the maximum date in your Actuals table, if so, return the forecast number, otherwise return the actuals number. Avoids adding both together.

 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for the replies @Greg_Deckler and @amitchandak . I'll try to give some better info this time 🙂

 

Starting with sample data from my 2 fact tables

Actuals

CustomerSKU CodeDateUnits amountGross Sales amountRebates amountMarketActuals or Forecast Type
1019534701/05/202020260020DomesticActual
1019534901/05/2020433150090DomesticActual
10113228401/05/20205610039DomesticActual
1014044101/06/202030526DomesticActual
1014045501/06/2020203515DomesticActual
1014449601/06/202036314DomesticActual
1019534701/07/202013950088DomesticActual
1019534901/07/20202891049110DomesticActual
10113228401/07/2020561009DomesticActual
1014044101/08/202078703DomesticActual
1014045501/08/202034602DomesticActual
1017006301/08/20203030DomesticActual


Forecasts

CustomerSKU CodeDateUnits amountGross Sales amountRebates amountMarketActuals or Forecast Type
10110689201/06/20206274190025DomesticForecast6
10110689201/06/20206274190025DomesticForecast6
10110689201/07/2020114263510102DomesticForecast6
10110689201/07/20205863199299DomesticForecast6
10110215601/08/2020582198298DomesticForecast6
10110215601/08/2020582198298DomesticForecast8
10110689201/08/2020153344802224DomesticForecast8
10110689201/09/2020148894663214DomesticForecast6
10110689401/09/202054771582145DomesticForecast6
10110689401/09/202054771582145DomesticForecast8
10110689201/10/202065982451240DomesticForecast6
10110689401/10/2020239772DomesticForecast8
10110215601/11/2020564192019DomesticForecast8
10110215601/11/2020564192020DomesticForecast8

 

 

I want to include values (Units, or Gross Sales, or Rebates) from both tables such that I have a continuous time line of actuals and forecast data, for each forecast type, that I can use in time intelligence measures (YTD, YoY etc.).

 

I can envisage a measure for each forecast type, say 'Actuals & Forecast 6' that:

 - includes actuals until the forecast starts and then forecast values thereafter. So for Forecast6 (which has forecast values from June), it would have Actuals until May (month 5) then Forecast6 values from June onwards.

 - excludes the Actuals values for months 6, 7 and 8.

 

However, each month there's a new forecast name, say Forecast9 and I don't want to have to create a new set of measures every month.

 

So far I have some simple measures, for example:

Gross Sales = SUM ( Actuals[Gross Sales amount] ) + SUM ( Forecasts[Gross Sales amount] )

and:

Gross Sales YTD = CALCULATE ( [Gross Sales], DATESYTD ( 'Calendar'[Date] ) )

 

These can be used with a slicer on 'Actuals or Forecast Type' and choosing 'Actuals' and 'Forecast6' for example. Aside from the double count issue already mentioned, there's a limitation with this; I can see the business then asking for a side by side comparison between 'Actuals & Forecast6' and 'Actuals & Forecast7' which I can't show with slicers as it would sum Actuals + Forecast6 + Forecast7.

 

Maybe my approach/understanding is wrong and there's a better way of doing this - suggestions welcomed! And also any help with the DAX for excluding those overlap Actuals months would be great.

 

I hope that explains the problem a little better. If not, just let me know what's unclear and I'll try to clarify.

 

Thanks,

 

Adrian

amitchandak
Super User
Super User

@_Adrian , can you share some same data. If they part of one table we can use the first nonblank value based on the version and take the value

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

 

Of we can use Switch based on not value from 1 plan to another.

 

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.