Forum Discussion
Last 3 days average values per ID using date
Hi all,
I am trying to calculate last 3 days average values per ID. Below is sample data set:
| modelcode | ordernumber | Date | DISCOUNTPERCENT | TMAX |
| E120XN | 12462 | 21-03-13 | 100 | 40 |
| E120XN | 260701 | 28-07-11 | 30 | 40 |
| E120XN | 260702 | 28-07-11 | 30 | 40 |
| E120XN | 260703 | 28-07-11 | 30 | 40 |
| E120XN | 260691 | 28-07-11 | 30 | 40 |
| E40HSD3 | 287077 | 04-05-11 | 42 | 42 |
| E40HSD3 | A17010 | 26-01-11 | 42 | 42 |
| E40HSD3 | A19142 | 30-04-11 | 42 | 42 |
| E40HSD3 | A10998 | 28-12-12 | 42 | 42 |
| E40HSD3 | A30618 | 21-04-11 | 42 | 42 |
| E40HSD3 | 273464 | 01-06-12 | 41.907 | 42 |
| E40HSD3 | 273465 | 01-06-12 | 41.907 | 42 |
| E55XN | 130120 | 01-11-10 | 45 | 42 |
| E55XN | 130122 | 01-11-10 | 46 | 42 |
| E55XN | 24131 | 09-06-12 | 99.999 | 42 |
| E55XN | 24130 | 15-06-12 | 43 | 42 |
| E55XN | 24129 | 09-06-12 | 99.999 | 42 |
I have calculated different variance of Tmax, and average of discount per ID and per day as there are many orders per day.
Avg Discount %C = DIVIDE(CALCULATE(sum(Hyster[DISCOUNTPERCENT]),ALLEXCEPT((Hyster),Hyster[modelcode], Hyster[Order Date].[Date] )), [Model CountC])
Then created if condition bucket for average discount and Tmax variances.
Bucket = Switch ( True(),
And( [Avg Discount %C]>=-10 ,[Avg Discount %C] < Hyster[Fixed TMax Var 5%]) , "Green",
And( [Avg Discount %C]>=Hyster[Fixed TMax Var 5%],[Avg Discount %C] < Hyster[Fixed TMax Var 15%]) , "Yellow",
And( [Avg Discount %C] >= Hyster[Fixed TMax Var 15%],[Avg Discount %C] < 500), "Red"
)This Bucket value is assigned to Modelcode ID per day based on the average disclount.
Now I am trying to take the last 3 days data per Model Code and want to apply another condition saying if last 3 days Model Code Bucket values are all Red's then Red, if any one value is Green then Green else Yellow.
For this I have created column :
Last 3T =
VAR __tmpTable = SUMMARIZE(FILTER(ALL('Hyster'),Hyster[modelcode]=EARLIER(Hyster[modelcode]) && Hyster[Order Date]<=EARLIER(Hyster[Order Date]) && Hyster[Order Date]>=EARLIER(Hyster[Order Date])-2),Hyster[Bucket])
VAR __rows = COUNTROWS(__tmpTable)
RETURN
IF(__rows = 1,MAXX(__tmpTable,Hyster[Bucket]),IF("Green" IN __tmpTable, "GG", IF("Yellow" IN __tmpTable, "Yellow", "Red")))But this is not giving the expected result.
The expected result should be Green in the Last 3T created column.
I have tried all possible ways, please help me in any other way possible.
Sample Data File: https://www.dropbox.com/s/hava20x6elmyb1u/SampleTest.xlsx?dl=0
Sample PBIX: https://www.dropbox.com/s/arsdivbe1mezf4a/SampleT.pbix?dl=0
Thank You,
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" ) )
18 Replies
I believe the issue that you are facing is that when I originally replied to this, I was thinking that you wanted the last three days as in the last three actual days. So, in other words if your date was 3/21/2013, the last three days would be 3/21/2013, 3/20/2013 and 3/19/2013. Possibly 3/18/2013 as well depending on your definition of "last three days". But, now that I am looking a bit closer at your data, I believe that is not what you want. What you want is the last three days in your data, so for E120XN for 3/21/2013 you want the 3/21/2013 date as well as the 7/28/2011 date, correct?
In that case, you are probably going to need to use RANKX and/or TOPN to filter out your SUMMARIZE temp table so that you can filter it to the most recent three days in your data.
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
