Forum Discussion

BobBI's avatar
BobBI
Resolver III
8 years ago
Solved

Create filter for Good or Bad performance

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

 

 

  • BobBI's avatar
    BobBI
    8 years ago

    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

     

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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's avatar
      BobBI
      Resolver III
      8 years ago

      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