Forum Discussion
Dynamic Measures Not Working As Should
I have a dashboard with hundreds of measures, so I opted to rid myself of them by using dynamic measures and the switch function.
When I use this measure with actual measures contained within the VARiables... it works. However, when trying to stack the VARiables contained within the measure itself, it does not work.
This works:
Inbound_Totals_SPLY_Dynamic =
VAR currentdate = LASTDATE(_Date[Date])
VAR daynumberofweek = WEEKDAY(LASTDATE(_Date[Date]),3)
VAR Inbound =CALCULATE(SUM('_Agent Performance'[Inbound]),USERELATIONSHIP(_Date[Date],'_Agent Performance'[Call Date]))
VAR Inbound_WTD =CALCULATE([Inbound],DATESBETWEEN(_Date[Date],DATEADD(currentdate,-1 * daynumberofweek,DAY),currentdate))
VAR Inbound_MTD =CALCULATE([Inbound],DATESMTD(_Date[Date]))
VAR Inbound_QTD =CALCULATE([Inbound],DATESQTD(_Date[Date]))
VAR Inbound_YTD =CALCULATE([Inbound],DATESYTD(_Date[Date]))
VAR Inbound_SPLY =CALCULATE([Inbound],SAMEPERIODLASTYEAR(_Date[Date]))
VAR Inbound_WTD_SPLY =CALCULATE([Inbound_WTD],SAMEPERIODLASTYEAR(_Date[Date]))
VAR Inbound_MTD_SPLY =CALCULATE([Inbound_MTD],SAMEPERIODLASTYEAR(_Date[Date]))
VAR Inbound_QTD_SPLY =CALCULATE([Inbound_QTD],SAMEPERIODLASTYEAR(_Date[Date]))
VAR Inbound_YTD_SPLY =CALCULATE([Inbound_YTD],SAMEPERIODLASTYEAR(_Date[Date]))
VAR SelectedMeasure = SELECTEDVALUE(_InContact_Measure_Table_SPLY[TableOrder])
RETURN
SWITCH(
SelectedMeasure,
5,Inbound_SPLY,
6,Inbound_WTD_SPLY,
7,Inbound_MTD_SPLY,
8,Inbound_QTD_SPLY,
9,Inbound_YTD_SPLY,
BLANK()
)This does not:
Inbound_Totals_SPLY_Dynamic =
VAR currentdate = LASTDATE(_Date[Date])
VAR daynumberofweek = WEEKDAY(LASTDATE(_Date[Date]),3)
VAR _Inbound =CALCULATE(SUM('_Agent Performance'[Inbound]),USERELATIONSHIP(_Date[Date],'_Agent Performance'[Call Date]))
VAR _Inbound_WTD =CALCULATE(_Inbound,DATESBETWEEN(_Date[Date],DATEADD(currentdate,-1 * daynumberofweek,DAY),currentdate))
VAR _Inbound_MTD =CALCULATE(_Inbound,DATESMTD(_Date[Date]))
VAR _Inbound_QTD =CALCULATE(_Inbound,DATESQTD(_Date[Date]))
VAR _Inbound_YTD =CALCULATE(_Inbound,DATESYTD(_Date[Date]))
VAR _Inbound_SPLY =CALCULATE(_Inbound,SAMEPERIODLASTYEAR(_Date[Date]))
VAR _Inbound_WTD_SPLY =CALCULATE(_Inbound_WTD,SAMEPERIODLASTYEAR(_Date[Date]))
VAR _Inbound_MTD_SPLY =CALCULATE(_Inbound_MTD,SAMEPERIODLASTYEAR(_Date[Date]))
VAR _Inbound_QTD_SPLY =CALCULATE(_Inbound_QTD,SAMEPERIODLASTYEAR(_Date[Date]))
VAR _Inbound_YTD_SPLY =CALCULATE(_Inbound_YTD,SAMEPERIODLASTYEAR(_Date[Date]))
VAR SelectedMeasure = SELECTEDVALUE(_InContact_Measure_Table_SPLY[TableOrder])
RETURN
SWITCH(
SelectedMeasure,
5,_Inbound_SPLY,
6,_Inbound_WTD_SPLY,
7,_Inbound_MTD_SPLY,
8,_Inbound_QTD_SPLY,
9,_Inbound_YTD_SPLY,
BLANK()
)The VAR SelectMeasure refers to a custom table I created to manage the measures/order, etc.
When
I am puzzled as to why this doesn't work. Can someone who is more knowledgeable tell me what the problem and the solution is? Thanks a bunch!
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() )
4 Replies
Well, I enjoy a game of spot-the-difference but maybe you can narrow it down a bit. Where exactly are they different?
Please see attached image for clarification
Thanks, I appreciate the clarification.
For the 2nd one, when this line is evaluated:
VAR _Inbound =CALCULATE(SUM('_Agent Performance'[Inbound]),USERELATIONSHIP(_Date[Date],'_Agent Performance'[Call Date]))_Inbound is a scalar value at this point. It's fixed and won't change.
It doesn't get re-evaluated in the subsequent variable assignments.
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() )
