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.
Hi 4thSun ,
The question on this is that your YTD value is dependent on all 3 measures meaning that until March the YTD corresponds to the budget but on November it corresponds to the Budget, Forecast 3_6, Forecast 6_9 and Forecast_9_12.
I looked into your previous post and since the budget and forecast are columns you should unpivot them, this will add a category column and the values alongside with any other categorization you need.
Assume something like this:
| Date | Category | Value |
|
01/01/2024 |
Budget | N |
| 01/02/2024 | Budget | N |
| 01/03/2024 | Budget | N |
| 01/04/2024 | Forecast_3_6 | N |
| ... | ... | ... |
| 01/12/2024 | Forecast_9_12 | N |
Not sure if this is the correct setup or you have a value for each of the budgets and forecast columns, but depending on the setup this will unpivot will allow you to do a YTD based on any value you need because they will have a corresponding value for each one.
If you have any further questions so it can be easier to get the correct answer, can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Hi MFelix - thanks for the initial response. My data setup is as formatted as above with category and values as columns.
I will see if i'm able to upload data sample for you; but in the meantime i'd break down expected results so the question is very clear:
STEP 1 - BREAKDOWN
1. Since Calculations groups were proving difficult, I decided to create individual measures and then see how I could do the same using calculation groups - where actuals = wins and FC is forecast
2. Data in the red boxes will be the correct data for my final table/matrix. YTD values for each quarter would reflect their respective values as well
3. For Q1 - April - June : Budget data -
4. For Q2 - Jul - Sept : 3+9 FC data - YTD values for 3+9 is a cumulative of the black box data (3+9 data essentially) to reflect the YTD values for Jul-Sept. (See how YTD for this time period is different from the budget period?)
5. Same for Q3 - Oct- Dec: 6+6 FC data - YTD values for 6+6 is a cumulative of the black box data (6+6 data essentially) to reflect the YTD values for Oct-Dec. (See how YTD for this time period is different from the the previous period?)
6. Same for 9+3 too.
7. The final outcome would show all the values in the red boxes.
STEP 2 - CALCULATION GROUPS (Here Comes the challenge)
1. Using calculation groups for time intelligence- I need to be able to return the correct YTD values for wins, winsFC, variance
2. I have used YTD = CALCULATE(SELECTEDMEASURE(), DATESYTD(DateTable[date]), "31/3")
3. This returns the right numbers for YTD Wins (as expected) but not for YTD wins FC or YTD Variance as seen below - looks like the formula doesnt look past the budget data as YTD wins FC has the same value after Jun.
4. The big question is how do I get calculation group to return the right values for YTD wins FC and YTD variance. Is there a way to do this when switch is used in a measure?
This is going to make my life alot easier if this works for the sake of other measures.
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.