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
Mr_Glister
Advocate II
Advocate II

Divide month value by December value

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 YearForecast dateFull-year sales volume forecast forecast vs year-end actual
201725.01.20171150 96 %
201706.03.20171130 94 %
201726.03.20171180 98 %
201705.05.20171230 103 %
201704.06.20171230 103 %
201704.07.20171240 103 %
201713.08.20171240 103 %
201722.09.20171260 105 %
201711.11.20171280 107 %
201727.12.20171200= year-end 2017 actual100 %
201805.01.20181200 103 %
201804.02.20181120 96 %
201816.03.20181140 97 %
201815.04.20181230 105 %
201825.05.20181210 103 %
201814.06.20181200 103 %
201824.07.20181280 109 %
201823.08.20181210 103 %
201802.09.20181230 105 %
201812.10.20181290 110 %
201821.11.20181230 105 %
201825.12.20181170= year-end 2018 actual100 %

 

And here you see expected outcome from my real data, but done in a very manual way with helper tables created in Excel etc...

 

Capture.JPG

 

 

 

 

 

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!

1 ACCEPTED 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!

 

 

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi, @Mr_Glister 

I think you need to use “all” and DATEADD functions in DAX:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

5.JPG

and here is pbix file, please try it.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

 

 

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.