last 3 Days Variance per ID

[anil_power]

Hi all,

         I have a dataset of ID, Date, and value. I am trying to calculate the last 3 values of ID based on the last 3 dates. So once we get the last 3 values based on these trying to write a condition. If all three values of an ID is the same eg red then "Red". If any of the value is red other than green then "Yellow" and anyone value is green then "Green".

This is the sample dataset I am working on:

 

These are the expected results:

 

The actual expected result.

   

Sample pbix : https://www.dropbox.com/s/7p5a9ba0g9znac6/Sample.pbix?dl=0

Please someone help me with a solution

Thank You

[Greg_Deckler]

Can you post your source example data as text? That's a lot to type manually.

[Greg_Deckler]

I can't really fathom your logic as to how you are getting red, yellow, green, I'm not comprehending your explanation of it. But it is going to be something like:

Last 3 = 
VAR __tmpTable = SUMMARIZE(FILTER(ALL('Sample'),[ID]=EARLIER([ID]) && [Date]<=EARLIER([Date]) && [Date]>=EARLIER([Date])-2),'Sample'[Bucket])
VAR __rows = COUNTROWS(__tmpTable)
RETURN
IF(__rows = 1,MAXX(__tmpTable,[Bucket]),IF("Yellow" IN __tmpTable, "Yellow", IF("Green" IN __tmpTable, "Green", "Red")))

[anil_power]

Greg,

         Can you please attach pbix file so it would be helpful. 

[Greg_Deckler]

Sure.

[anil_power]

Thanks Greg, your reply was really helpful.

The Bucket column in data I am calculated using below formula. 

Bucket = Switch ( True(),

And( [Avg Discount %]>=-10 ,[Avg Discount %] < [TMax 5% Var]) , "Green",
And( [Avg Discount %]>=[TMax 5% Var],[Avg Discount %] < [TMax 15% Var]) , "Yellow",
And( [Avg Discount %] >= [TMax 15% Var],[Avg Discount %] < 500), "Red"

)

and I have used the calculated column formula given by you, but I am facing dependency error while applying the formula

 

[v-piga-msft]

Hi @anil_power,

Please refer to this blog to avoid A circular Dependency was detected" error.

Best Regards，

Cherry

[anil_power]

Hi Greg,

           Thanks for the reply. Below are the sample files.

Sample excel file: https://www.dropbox.com/s/umb808dhuay7jjw/Sample.xlsx?dl=0

Sample pbix : https://www.dropbox.com/s/7p5a9ba0g9znac6/Sample.pbix?dl=0