Forum Discussion

stevie_westside's avatar
2 years ago
Solved

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 th...
  • stevie_westside's avatar
    stevie_westside
    2 years ago

    AI figured out my oversight...
    "...the variables Inbound_Handled_WTD_SPLYInbound_Handled_MTD_SPLYInbound_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_WTDInbound_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()
        )