Forum Discussion
Last 3 days average values per ID using date
OK, first, you need to clean up your PBIX, you have some columns causing circular dependencies, like "If test". Get rid of that column. Also, for some odd reason it wasn't getting the correct modelcode at the beginning so I fixed that and cleaned up some code.
Last 3TEst = VAR __modelcode = MAXX(CALCULATETABLE('Hyster'),'Hyster'[modelcode]) VAR __tmpTable = SUMMARIZE(FILTER(ALL('Hyster'),Hyster[modelcode]=__modelcode),[Order Date],[Bucket]) VAR __maxDate = MAXX(__tmpTable,[Order Date]) VAR __tmpTable2 = TOPN(3,__tmpTable,[Order Date],DESC) VAR __tmpTable3 = SELECTCOLUMNS(__tmpTable2,"__Bucket",[Bucket]) RETURN IF( CONTAINS(__tmpTable3,[__Bucket],"Green"), "Green", IF( CONTAINS(__tmpTable3,[__Bucket],"Yellow"), "Yellow", "Red" ) )
See if this works. First, create this calculated column:
Rank = RANKX(ALL(Hyster),[Order Date])
Then modify your measure:
Last 3T =
VAR __tmpTable = SUMMARIZE(FILTER(ALL('Hyster'),Hyster[modelcode]=EARLIER(Hyster[modelcode]) && Hyster[Order Date]<=EARLIER(Hyster[Order Date])),Hyster[Bucket])
VAR __tmpTable2 = TOPN(3,__tmpTable,1)
VAR __rows = COUNTROWS(__tmpTable)
RETURN
IF(__rows = 1,MAXX(__tmpTable2,Hyster[Bucket]),IF("Green" IN __tmpTable, "GG", IF("Yellow" IN __tmpTable, "Yellow", "Red")))
Thank You Greg :smileyhappy:
Your solution was really helpful.
Hi Greg,
In the solution you have provided the expected result is copied to the max date cell of the model code. But can we copy the expected result to all rows of the model code?
If you see in the above image, the last column should have only "Green" but there is "Red" also. Can we copy the expected result "Green" to all other rows of the Model Code?
Sample PBIX file : https://www.dropbox.com/s/p0olu6tcy43ltlu/SampleT2.pbix?dl=0
OK, I'm still not sure I'm "getting" your logic for your last 3 calculation. So the way it works now is that it takes the MAX of the date row that it is in. It looks back through the last 2 dates prior to that (regardless of how long) and returns a category label essentially. So, on the first row, if you look at that row and the 2 previous you have Yellow, Green, Red. Since there is a Green, it is Green. In the second row, you would have Green and Red. Since there is a Green, it is Green. In the last row, you have only Red (there are no previous dates), so it is Red.
So, now what you are saying is that you don't care about the "last 3 days", we already established that it is the "last 3 dates", but you don't want that either, what it sounds like you really want is to always look at "the most recent 3 dates", correct? So, in essence, always get the MAX of all of the dates for an ID and then look at the previous 2 dates before that and determine the bucket. Do this regardless of how far back in time, so if you had 5 dates, all 5 would effectively be the exact same calculation, get the most recent and look back 2 days. Is that correct?
