cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mr_Glister Regular Visitor
Regular Visitor

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

Accepted Solutions
Mr_Glister Regular Visitor
Regular Visitor

Re: Divide month value by December value

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
Community Support Team
Community Support Team

Re: Divide month value by December value

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.
Mr_Glister Regular Visitor
Regular Visitor

Re: Divide month value by December value

Thanks Lin, I added sample data!

Highlighted
Community Support Team
Community Support Team

Re: Divide month value by December value

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.
Mr_Glister Regular Visitor
Regular Visitor

Re: Divide month value by December value

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 157 members 1,535 guests
Please welcome our newest community members: