Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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.
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