cancel
Showing results for
Did you mean:
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 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!

1 ACCEPTED SOLUTION

Accepted Solutions
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]))))))`

4 REPLIES 4
Highlighted
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...

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

## Re: Divide month value by December value

Thanks Lin, I added sample data!

Community Support Team

## Re: Divide month value by December value

hi, @Mr_Glister

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

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.
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]))))))`

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)