Forum Discussion
SWITCH function in measure returns wrong value in YTD calculation group
As a follow-up to my previous question which Angith_Nair promptly responded to: https://community.fabric.microsoft.com/t5/Desktop/Financial-reporting-Budget-3-9-forecast-6-6-forecast-and-9-3/m-p/4276750#M1343037 ;
I want to use the below measures: actuals, selectedForecast, and variance in a calculation group to return YTD values;
Budget = SUM('ForecastTable'[Budget])
Forecast_3_9 = SUM('ForecastTable'[Forecast_3_9])
Forecast_6_6 = SUM('ForecastTable'[Forecast_6_6])
Forecast_9_3 = SUM('ForecastTable'[Forecast_9_3])SelectedForecast =
VAR CurrentMonth = MONTH(SELECTEDVALUE('DateTable'[Date]))
RETURN
SWITCH(
TRUE(),
CurrentMonth <= 3, [Budget],
CurrentMonth <= 6, [Forecast_3_9],
CurrentMonth <= 9, [Forecast_6_6],
[Forecast_9_3]
)Variance =
[Actuals] - [SelectedForecast]this formula works for YTD actuals
= CALCULATE(SELECTEDMEASURE(), DATESYTD(DateTable[date]), "31/3")
but doesn't work for YTD selectedForecast (as it only returns budget values - doesn't seem to see 3+9, 6+6, etc) and YTD variance - I think this is because of the switch function
Is this really possible in Calculation groups or this cant be done?
The whole idea of wanting to use calculation groups is so that I could use it on other measures like values, deals, conversions, wins without having to write 1001 measures. Any tips would be greatly appreaciated.
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.
10 Replies
- MFelix
Super User
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.
- MFelix
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 - I have added two links to the mock up file to support my above response. Please do let me know which one works for you. Thanks!
Forecast Mockup.pbixhttps://app.powerbi.com/onedrive/open?pbi_source=ODSPViewer&driveId=b!4oDXw8DNwECv580jxbvzaCxO_EaQFN9Ph9hVJFqL1IDCHovHMIs7SpBtAXV21SeG&itemId=01AWBN5RMXAWTIXDHOWREZEBJTDWZYRTEIMFelix , @Angith_Nair - this link should work now: Forecast Mockup.pbix
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.
MFelix, thank you so much for your time and effort! The YTD and variance work as they should now. I have only changed the VAR currentmonth to month(max(Dim_date[Date])) instead of month(selectedvalue (Dim_date[Date])) from my initial approach and it works like a charm.
For others looking for Budget and Forecast inspiration; my approach or MFelix approach works just fine depending on your use-case.
