Dynamic percent change on power bi is not returning a value

Question

I am trying to return a percentage change on spend for when I drill into different levels of my date hierarchy called CLM_THRU_DT. It is not returning a value or error at the moment, just a blank column or measure.

This is the code that I have been trying. Any help would be appreciated.

% Change column =

VAR PreviousValue =
  SWITCH( True(),
    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Day]),
      CALCULATE(
        SUM(AverageExpense_Grouptin[Spend]),
        DATEADD('AverageExpense_Grouptin'[CLM_THRU_DT],-1,DAY)
      ),

    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Month]),
      CALCULATE(
        SUM(AverageExpense_Grouptin[Spend]), 
        PARALLELPERIOD('AverageExpense_Grouptin'[CLM_THRU_DT],-1,Month)
      ),

    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Quarter]), 
      CALCULATE(
        SUM(AverageExpense_Grouptin[Spend]), 
        PARALLELPERIOD('AverageExpense_Grouptin'[CLM_THRU_DT],-1,Quarter)
      ),

    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Year]), 
      CALCULATE(
        SUM(AverageExpense_Grouptin[Spend]), 
        PARALLELPERIOD('AverageExpense_Grouptin'[CLM_THRU_DT],-1,YEAR)
      )
  )

RETURN
  DIVIDE( 
    SUM(AverageExpense_Grouptin[Spend]) - PreviousValue,
    PreviousValue
  )

Answer 1

You will need to add .[Date] to your DATEADD and PARALLELPERIOD for it to work. For example:

PARALLELPERIOD('AverageExpense_Grouptin'[CLM_THRU_DT].[Date], -1, Month)

My suggestion for you. Create these two measures first:

$ Spend = SUM(AverageExpense_Grouptin[Spend])

$ Spend PrevPeriod =
  SWITCH( True(),
    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Day]),
      CALCULATE([$ Spend], PREVIOUSDAY(AverageExpense_Grouptin[CLM_THRU_DT].[Date])),

    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Month]),
      CALCULATE([$ Spend], PREVIOUSMONTH(AverageExpense_Grouptin[CLM_THRU_DT].[Date])),

    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Quarter]), 
      CALCULATE([$ Spend], PREVIOUSQUARTER(AverageExpense_Grouptin[CLM_THRU_DT].[Date])),

    ISINSCOPE(AverageExpense_Grouptin[CLM_THRU_DT].[Year]), 
      CALCULATE([$ Spend], PREVIOUSYEAR(AverageExpense_Grouptin[CLM_THRU_DT].[Date]))
  )

Then update your measure to:

% Change column =
  DIVIDE(
    [$ Spend] - [$ Spend PrevPeriod],
    [$ Spend PrevPeriod]
  )