Forum Discussion
Dynamic Measures Not Working As Should
- 2 years ago
AI figured out my oversight...
"...the variables Inbound_Handled_WTD_SPLY, Inbound_Handled_MTD_SPLY, Inbound_Handled_QTD_SPLY, and Inbound_Handled_YTD_SPLY might be causing an issue. They are attempting to calculate the SPLY values for WTD, MTD, QTD, and YTD by using the CALCULATE function with the SAMEPERIODLASTYEAR function, but they are referencing the already calculated totals (Inbound_Handled_WTD, Inbound_Handled_MTD, etc.) instead of the base measure [Inbound_Handled]. This could lead to incorrect results because SAMEPERIODLASTYEAR should be applied directly to the base measure within the context of the CALCULATE function.
"The issue here is that you’re using the already calculated Inbound_Handled_WTD instead of the base measure [Inbound_Handled] within the CALCULATE function.
To fix this, replace Inbound_Handled_WTD with [Inbound_Handled] in the CALCULATE function for the SPLY calculation:Inbound_Handled_SPLY_Dynamic = VAR currentdate = LASTDATE(_Date[Date]) VAR daynumberofweek = WEEKDAY(LASTDATE(_Date[Date]),3) VAR Inbound_Handled_WTD =CALCULATE([Inbound_Handled],DATESBETWEEN(_Date[Date],DATEADD(currentdate,-1 * daynumberofweek,DAY),currentdate)) VAR Inbound_Handled_MTD =CALCULATE([Inbound_Handled],DATESMTD(_Date[Date])) VAR Inbound_Handled_QTD =CALCULATE([Inbound_Handled],DATESQTD(_Date[Date])) VAR Inbound_Handled_YTD =CALCULATE([Inbound_Handled],DATESYTD(_Date[Date])) VAR Inbound_Handled_SPLY = CALCULATE([Inbound_Handled],SAMEPERIODLASTYEAR(_Date[Date])) VAR Inbound_Handled_WTD_SPLY = CALCULATE([Inbound_Handled],SAMEPERIODLASTYEAR(DATESBETWEEN(_Date[Date], DATEADD(currentdate, -1 * daynumberofweek, DAY), currentdate))) VAR Inbound_Handled_MTD_SPLY = CALCULATE([Inbound_Handled],SAMEPERIODLASTYEAR(DATESMTD(_Date[Date]))) VAR Inbound_Handled_QTD_SPLY = CALCULATE([Inbound_Handled],SAMEPERIODLASTYEAR(DATESQTD(_Date[Date]))) VAR Inbound_Handled_YTD_SPLY = CALCULATE([Inbound_Handled],SAMEPERIODLASTYEAR(DATESYTD(_Date[Date]))) VAR SelectedMeasure = SELECTEDVALUE(_InContact_Measure_Table_SPLY[TableOrder]) RETURN SWITCH( SelectedMeasure, 20,Inbound_Handled_SPLY, 21,Inbound_Handled_WTD_SPLY, 22,Inbound_Handled_MTD_SPLY, 23,Inbound_Handled_QTD_SPLY, 24,Inbound_Handled_YTD_SPLY, BLANK() )
Please see attached image for clarification