Forum Discussion
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!
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!