Forum Discussion
last 3 Days Variance per ID
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
7 Replies
Can you post your source example data as text? That's a lot to type manually.
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
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")))Greg,
Can you please attach pbix file so it would be helpful.
