Forum Discussion
SWITCH function in measure returns wrong value in YTD calculation group
Hi Anonymous ,
I have done the following:
- Created a new column on the Wins forecast table:
Category Adjusted = var _Category = SWITCH( TRUE(), MONTH([date_month]) <= 6 && MONTH([date_month]) > 3, "BUD", MONTH([date_month]) <= 9 && MONTH([date_month]) > 6, "3+9", MONTH([date_month]) <= 12 && MONTH([date_month]) > 9, "6+6" ) RETURN IF (_Category = 'Wins forecast'[category], _Category)Add this new measure:
Wins FC NEW MEASURE = CALCULATE(SUM('Wins forecast'[wins]),'Wins forecast'[Category Adjusted] <> "")This will allow to have the calculation of the forecast without the need for the switch measure, you can see that the result will be correct based on the selection:
See file attach.
This setup forces the values to show up only for the specific moths were each forecast is used and not on the rest of the year where the values are for budget or for other forecast.
I did the new column in DAX but you can also do it in M Query that is more advisable.
MFelix - thanks for that feedback. I get what you did there, but I need this as seen in the red boxes for YTD wins FC: I need the cumulative of wins FC(3+9) to reflect in the YTD section for that time-period. Same as wins FC (6+6)
and then for YTD variance for the period 3+9 this is what I'm looking for -
and for YTD 6+6
....at the end of the day, this is what I need in a calculation group format.
Hi 4thSun you want to have the cumulative based on the forecast cumulative and not on the month value is that it?