Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anil
Helper III
Helper III

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:

 

modelcodeordernumberDateDISCOUNTPERCENTTMAX
E120XN1246221-03-1310040
E120XN26070128-07-113040
E120XN26070228-07-113040
E120XN26070328-07-113040
E120XN26069128-07-113040
E40HSD328707704-05-114242
E40HSD3A1701026-01-114242
E40HSD3A1914230-04-114242
E40HSD3A1099828-12-124242
E40HSD3A3061821-04-114242
E40HSD327346401-06-1241.90742
E40HSD327346501-06-1241.90742
E55XN13012001-11-104542
E55XN13012201-11-104642
E55XN2413109-06-1299.99942
E55XN2413015-06-124342
E55XN2412909-06-1299.99942

 

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.

 

Bucket.JPG

 

 

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.

 

exp.JPG

 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,

 

 

 

1 ACCEPTED SOLUTION

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

18 REPLIES 18
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank You Greg Smiley Happy

              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?

T6.JPG

 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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,
Once we get the result for last 3 days, say green can we copy the same green value for all the remaining days?
Or in a new column, so for the model code ID based on the last 3 days calculation the end result is green for example.

My thought is if we take max date for each Model Code ID and take the calculated bucket value. So we get the expected result value per Model Code ID at max date row. Can we copy the result value to all the rows of the Model Code ID

That's not really how it works. Let me adjust the formula based upon my new understanding.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

OK, how about this:

 

Last 3TEst = 
VAR __modelcode = MAX([modelcode])
VAR __tmpTable = FILTER(ALL('Hyster'),Hyster[modelcode]=__modelcode)
VAR __maxDate = MAXX(__tmpTable,[Order Date])
VAR __tmpTable2 = TOPN(3,__tmpTable,[Order Date],DESC)
VAR __tmpTable3 = SELECTCOLUMNS(__tmpTable2,"__Bucket",[Bucket])
VAR __rows = COUNTROWS(__tmpTable)
RETURN
IF(__rows = 1,MAXX(__tmpTable2,Hyster[Bucket]),IF(CONTAINS(__tmpTable3,[__Bucket],"Green"), "Green", IF(CONTAINS(__tmpTable3,[__Bucket],"Yellow"), "Yellow", "Red")))

PBIX attached.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

               Thanks for the reply.

I am getting incorrect results when used the calculation provided by you. Please look into the attached PBIX.

 PBIX file: https://www.dropbox.com/s/2bu5kt1l6k3nlvy/SampleT3.pbix?dl=0

 

 

E1.JPGE2.JPGE3.JPG 

 

 

 

 PBIX file: https://www.dropbox.com/s/2bu5kt1l6k3nlvy/SampleT3.pbix?dl=0

Not sure, it was working in the PBIX I attached to the last reply.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Dear Greg, Thank you so much. Now everything is working fineSmiley Very Happy

It's very kind of you Sir.

Yeah, I forgot that since you are using a calculated column that the first variable has to be calculated a little differently. Glad we got there.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg,

        Is it possible to color code the created column based on the result.Eg if its Green then green color, if Yellow yello color else Red color.

        I am unable to create measure using the calculated column. If we assign a number to each result of the column. Using that we can color the column.

That code should work as a measure. You might change the first variable to a simple MAX of the column. I'll have to check, on my phone at the moment.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

             If we want to slice by date and by selected month can we calculate last 3 days results?

Thanks Greg, I have created measure and using it colored the column.

Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.