Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@_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.
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
Customer | SKU Code | Date | Units amount | Gross Sales amount | Rebates amount | Market | Actuals or Forecast Type |
101 | 95347 | 01/05/2020 | 202 | 600 | 20 | Domestic | Actual |
101 | 95349 | 01/05/2020 | 433 | 1500 | 90 | Domestic | Actual |
101 | 132284 | 01/05/2020 | 56 | 100 | 39 | Domestic | Actual |
101 | 40441 | 01/06/2020 | 30 | 52 | 6 | Domestic | Actual |
101 | 40455 | 01/06/2020 | 20 | 35 | 15 | Domestic | Actual |
101 | 44496 | 01/06/2020 | 3 | 63 | 14 | Domestic | Actual |
101 | 95347 | 01/07/2020 | 139 | 500 | 88 | Domestic | Actual |
101 | 95349 | 01/07/2020 | 289 | 1049 | 110 | Domestic | Actual |
101 | 132284 | 01/07/2020 | 56 | 100 | 9 | Domestic | Actual |
101 | 40441 | 01/08/2020 | 78 | 70 | 3 | Domestic | Actual |
101 | 40455 | 01/08/2020 | 34 | 60 | 2 | Domestic | Actual |
101 | 70063 | 01/08/2020 | 30 | 3 | 0 | Domestic | Actual |
Forecasts
Customer | SKU Code | Date | Units amount | Gross Sales amount | Rebates amount | Market | Actuals or Forecast Type |
101 | 106892 | 01/06/2020 | 6274 | 1900 | 25 | Domestic | Forecast6 |
101 | 106892 | 01/06/2020 | 6274 | 1900 | 25 | Domestic | Forecast6 |
101 | 106892 | 01/07/2020 | 11426 | 3510 | 102 | Domestic | Forecast6 |
101 | 106892 | 01/07/2020 | 5863 | 1992 | 99 | Domestic | Forecast6 |
101 | 102156 | 01/08/2020 | 582 | 1982 | 98 | Domestic | Forecast6 |
101 | 102156 | 01/08/2020 | 582 | 1982 | 98 | Domestic | Forecast8 |
101 | 106892 | 01/08/2020 | 15334 | 4802 | 224 | Domestic | Forecast8 |
101 | 106892 | 01/09/2020 | 14889 | 4663 | 214 | Domestic | Forecast6 |
101 | 106894 | 01/09/2020 | 5477 | 1582 | 145 | Domestic | Forecast6 |
101 | 106894 | 01/09/2020 | 5477 | 1582 | 145 | Domestic | Forecast8 |
101 | 106892 | 01/10/2020 | 6598 | 2451 | 240 | Domestic | Forecast6 |
101 | 106894 | 01/10/2020 | 239 | 77 | 2 | Domestic | Forecast8 |
101 | 102156 | 01/11/2020 | 564 | 1920 | 19 | Domestic | Forecast8 |
101 | 102156 | 01/11/2020 | 564 | 1920 | 20 | Domestic | Forecast8 |
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
@_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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |