Forum Discussion
comparing deviations of multiple points in time
So finally after trying and tweaking a lot here and there I got the solution which works for me 🙂
Still huge thanks v-xuding-msft for guiding me into the right direction 🤘.
below formula works perfectly. Hope it helps someone someday 😀.
Flag =
var a= CALCULATE(SUM(Table[deviation]),FILTER(Table, Table[month/year].[Year]=[prev year] && Table[month/year].[Month]=EARLIER(Table[month/year].[Month]) && Table[key1]=EARLIER(Table[key1]) && Table[key2]=EARLIER(Table[key2]) ))
var b= CALCULATE(SUM(Table[deviation]),FILTER(ALL(Table), Table[month/year].[Year]=[curr year] && Table[month/year].[Month]=EARLIER(Table[month/year].[Month]) && Table[key1]=EARLIER(Table[key1]) && Table[key2]=EARLIER(Table[key2]) ))
var c= CALCULATE(SUM(Table[deviation]),FILTER(ALL(Table), Table[month/year].[Year]=[new year] && Table[month/year].[Month]=EARLIER(Table[month/year].[Month]) && Table[key1]=EARLIER(Table[key1]) && Table[key2]=EARLIER(Table[key2]) ))
return IF( a>=3*b || b>=3*a || a>=3*c || c>=3*a || b>=3*c || c>=3*b, "True", "False")
Hi v-xuding-msft Xue,
your formula comes closer than anything I had tried uptill now, but it doesnt fit quite correctly and that is because the dataset I uploaded did not, to full extent, shows the complexity I'm dealing with😥.
The evaluation needs to be done on monthly basis, i.e. i need to compare if the deviation in 09.2020 is more than three times the deviation observed in 09.2018 and 09.2019. again unique tuple is the combination of key1 and key2
I have uploaded a better dataset with the expected result that I want to achieve.
When I applied your formula to this one, it did'nt quite bring the exactly appropriate result. May be you could suggest something for this...
Your help is really appreciated 😇
So finally after trying and tweaking a lot here and there I got the solution which works for me 🙂
Still huge thanks v-xuding-msft for guiding me into the right direction 🤘.
below formula works perfectly. Hope it helps someone someday 😀.
Flag =
var a= CALCULATE(SUM(Table[deviation]),FILTER(Table, Table[month/year].[Year]=[prev year] && Table[month/year].[Month]=EARLIER(Table[month/year].[Month]) && Table[key1]=EARLIER(Table[key1]) && Table[key2]=EARLIER(Table[key2]) ))
var b= CALCULATE(SUM(Table[deviation]),FILTER(ALL(Table), Table[month/year].[Year]=[curr year] && Table[month/year].[Month]=EARLIER(Table[month/year].[Month]) && Table[key1]=EARLIER(Table[key1]) && Table[key2]=EARLIER(Table[key2]) ))
var c= CALCULATE(SUM(Table[deviation]),FILTER(ALL(Table), Table[month/year].[Year]=[new year] && Table[month/year].[Month]=EARLIER(Table[month/year].[Month]) && Table[key1]=EARLIER(Table[key1]) && Table[key2]=EARLIER(Table[key2]) ))
return IF( a>=3*b || b>=3*a || a>=3*c || c>=3*a || b>=3*c || c>=3*b, "True", "False")