comparing deviations of multiple points in time

[Anonymous]

Hi Pbi Community,

I feel I'm stuck at a calculation I need to perform, may be it is the complexity of the data or something else but I just can't wrap my head around it.

 

So my data looks something like the image here. Key 1 and key2 together determine a unique category for which I need to do my evaluation. In my original dataset I have 3 years of data: 2018,2019 and 2020. I want to calculate a flag to be true if the deviation of the past for a category is more than 3 times different from the deviation of the future. I have an element in my file called the 'Last refresh date' which can be used to make the evaluation based on past and present. 

Probably someone out there can propose a viable solution to this.  my mind is completely swamped.

[v-xuding-msft]

Hi @Anonymous ,

I add some data based on your table.  Please check the sample and have a try.

Flag = var a = CALCULATE(SUM('Table'[deviation]),FILTER('Table','Table'[Date].[Year] = 2019))
var b = CALCULATE(SUM('Table'[deviation]),FILTER('Table','Table'[Date].[Year] = 2020))
var c = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[Key1],'Table'[Key2]),a<>b))
return
IF(c>=3,"Ture","Flase")

  

If this is not what you want, can you please share your expected result? Then we will understand clearly about your requirement.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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 😇

[Anonymous · Accepted solution]

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")