Create filter for Good or Bad performance

[BobBI]

Hello everyone,

I have data something like this 

Name       Date             value

A             1-Jan-18        20

B             1-Jan-18        35

C             1-Jan-18        15

A             1-Feb-18        10

B             1-Feb-18        15

C             1-Feb-18        35

A             1-Mar-18        11

B             1-Mar-18        25

C             1-Mar-18        05

My requirement is to create a filter with Good/Bad,  if the difference between current month value and ( average of last 2 months) is within +-10% then Good  otherwise Bad.

Difference =
VAR BASELINE_VALUE = CALCULATE(Value,
                      DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-1,Month))/2

VAR MEASURE_VALUE = Value (current month)
RETURN
 DIVIDE(MEASURE_VALUE-BASELINE_VALUE,BASELINE_VALUE)

doesn't give me correct result.

Any thoughts on this please.

Thanks

BB

[Phil_Seamark]

HI @BobBI

A good debugging technique is to break down your calculation in to smaller steps and asses the output of each variable and then check if it has the number you expect it to.

So try this first

Difference =
VAR BASELINE_VALUE = CALCULATE(Value,
                      DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-1,Month))/2

VAR MEASURE_VALUE = Value (current month)
RETURN
     MEASURE_VALUE

Then try this

Difference =
VAR BASELINE_VALUE = CALCULATE(Value,
                      DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-1,Month))/2

VAR MEASURE_VALUE = Value (current month)
RETURN
      BASELINE_VALUE

And this will at least let you understand which part of the calculation to focus on.

[BobBI · Accepted solution]

Hi Phil,

Thanks for your response , I have tried all this beofre my post. it give me correct result for bottom level filter as my selection goes up it starting giving me wrong values. However i am able to resolve this issue by creating a calculated table.

Thanks,

BB