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.
Hi,
one to three times each month (random dates each month) my company makes a full year sales volume forecast. As the year proceeds a higher proportion of the full year forecast is made up of actuals and one can expect that the forecast gets more accurate. (On Dec 30 it's quite easy to estimate what the full year sales volume will be...). Our last forecast at the end of December (somewhere between 25 - 30/12) is good enough for me to consider as the actual for the year.
I have historic data and want to analyse our forecast accuracy/performance. My idea is to divide the average (since there can be multiple) full-year volume forecast each month by the last value from December, then show the result in a line chart with Month as x-axis and Forecast Year as Legend.
I have a three columns in my table. Forecast Year // Forecast date // Full-year sales volume forecast
In the table below in the first 3 columns I created some dummy data. (My real data has 10 years and between 1-3 forecasts each month!). The fifth column is just there to make you understand what I'm after. For 2017 it would mean that in January we believed for the full year we would sell 4% less than we actually did (1150pc vs 1200pc).
Forecast Year | Forecast date | Full-year sales volume forecast | forecast vs year-end actual | |
2017 | 25.01.2017 | 1150 | 96 % | |
2017 | 06.03.2017 | 1130 | 94 % | |
2017 | 26.03.2017 | 1180 | 98 % | |
2017 | 05.05.2017 | 1230 | 103 % | |
2017 | 04.06.2017 | 1230 | 103 % | |
2017 | 04.07.2017 | 1240 | 103 % | |
2017 | 13.08.2017 | 1240 | 103 % | |
2017 | 22.09.2017 | 1260 | 105 % | |
2017 | 11.11.2017 | 1280 | 107 % | |
2017 | 27.12.2017 | 1200 | = year-end 2017 actual | 100 % |
2018 | 05.01.2018 | 1200 | 103 % | |
2018 | 04.02.2018 | 1120 | 96 % | |
2018 | 16.03.2018 | 1140 | 97 % | |
2018 | 15.04.2018 | 1230 | 105 % | |
2018 | 25.05.2018 | 1210 | 103 % | |
2018 | 14.06.2018 | 1200 | 103 % | |
2018 | 24.07.2018 | 1280 | 109 % | |
2018 | 23.08.2018 | 1210 | 103 % | |
2018 | 02.09.2018 | 1230 | 105 % | |
2018 | 12.10.2018 | 1290 | 110 % | |
2018 | 21.11.2018 | 1230 | 105 % | |
2018 | 25.12.2018 | 1170 | = year-end 2018 actual | 100 % |
And here you see expected outcome from my real data, but done in a very manual way with helper tables created in Excel etc...
Long story short - I don't manage to write the correct measure for the value field in the line chart, without my very manual helper tables. Can somebody help me?
P.S.: I'm not really interested in work-arounds. I would like to see the smart DAX measure that can deal with this simple looking problem.
Thanks for your help guys and girls!
Solved! Go to Solution.
Hi Lin,
this is a good start but like I said there can be multiple forecasts per month, also for December.
So for this we would have to change your formula to the following to sort of fix it.
Column = DIVIDE('Table'[Full-year sales volume forecast], CALCULATE(AVERAGE('Table'[Full-year sales volume forecast]),FIL...
But really, also this isn't correct because I wouldn't want to compare the monthly forecasts with the average of the December forecasts but only with the last one in that month/of the year, which is the actual for the year. If I used the formula as it is above and there are multiple forecasts in December I would see that none of the results in Column are exactly 100%. (If you add another row with a date in December 2018 to the sample data you will understand the problem right away.)
Your suggestion holds the solution to that as well and the final formula has to be:
Column = DIVIDE('Table'[Full-year sales volume forecast], CALCULATE(SUM('Table'[Full-year sales volume forecast]),FILTER('Table','Table'[Forecast Year]=EARLIER('Table'[Forecast Year])&&'Table'[Forecast Date]=CALCULATE(MAX('Table'[Forecast Date]),FILTER('Table','Table'[Forecast Year]=EARLIER('Table'[Forecast Year]))))))
Thanks for your help!
hi, @Mr_Glister
I think you need to use “all” and DATEADD functions in DAX:
Sample data and expected output would help tremendously. 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
Best Regards,
Lin
Thanks Lin, I added sample data!
hi, @Mr_Glister
For your requirement, you'd better add a new column as below:
Step1:
Add a month column for Forecast date
Forecast Month = MONTH('Table'[Forecast date])
Step2:
Use this formula to add a new column
Column = DIVIDE('Table'[Full-year sales volume forecast], CALCULATE(SUM('Table'[Full-year sales volume forecast]),FILTER('Table','Table'[Forecast Year]=EARLIER('Table'[Forecast Year])&&'Table'[Forecast Month]=12)))
Result:
and here is pbix file, please try it.
Best Regards,
Lin
Hi Lin,
this is a good start but like I said there can be multiple forecasts per month, also for December.
So for this we would have to change your formula to the following to sort of fix it.
Column = DIVIDE('Table'[Full-year sales volume forecast], CALCULATE(AVERAGE('Table'[Full-year sales volume forecast]),FIL...
But really, also this isn't correct because I wouldn't want to compare the monthly forecasts with the average of the December forecasts but only with the last one in that month/of the year, which is the actual for the year. If I used the formula as it is above and there are multiple forecasts in December I would see that none of the results in Column are exactly 100%. (If you add another row with a date in December 2018 to the sample data you will understand the problem right away.)
Your suggestion holds the solution to that as well and the final formula has to be:
Column = DIVIDE('Table'[Full-year sales volume forecast], CALCULATE(SUM('Table'[Full-year sales volume forecast]),FILTER('Table','Table'[Forecast Year]=EARLIER('Table'[Forecast Year])&&'Table'[Forecast Date]=CALCULATE(MAX('Table'[Forecast Date]),FILTER('Table','Table'[Forecast Year]=EARLIER('Table'[Forecast Year]))))))
Thanks for your help!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |