Forum Discussion

collmomo's avatar
collmomo
Advocate I
9 years ago
Solved

Filter Based on modified Measure

Hello, I have this Measure

 

Sales CY = CALCULATE(-SUM(Actual[Amount]),FILTER(Years,Years[YearID]=[Current Year]))
Current Year = VALUES(Years[YearID])

IIt works. However, if I try to modify the measure such as

Sales LY = CALCULATE(-SUM(Actual[Amount]),FILTER(Years,Years[YearID]=[Last Year]))
Last Year = [Current Year] - 1

Returns a blank value, any idea why ?

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    collmomo

     

    In this scenario, you need to add ALL() to ignore the current row context slicing.

     

    Last Year Amount = var currentyear =MAX(Table7[Year])
return
CALCULATE(SUM(Table7[Amount]),FILTER(ALL(Table7),Table7[Year]=currentyear-1))

     

     

    Regards,

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    collmomo

     

    In this scenario, you need to add ALL() to ignore the current row context slicing.

     

    Last Year Amount = var currentyear =MAX(Table7[Year])
return
CALCULATE(SUM(Table7[Amount]),FILTER(ALL(Table7),Table7[Year]=currentyear-1))

     

     

    Regards,