Forum Discussion

anil's avatar
anil
Helper III
8 years ago
Solved

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...
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

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

     

anil's avatar
anil
Helper III
8 years ago
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.
anil's avatar
anil
Helper III
8 years ago
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
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      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.