Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

max value from last 14 days

Hi All,   I have below data.   for each row i need to look back 14 days and pick which is max value and display it. pls see data below. 3rd column is the result i am looking for can you please t...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try with help from a date table

    Rolling 14 = CALCULATE(max(Table[Value.high]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,Day))

     

    or

     

    Rolling 14 = if(CALCULATE(distinctcount(Table[Value.tradingDay]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,Day)) >=14, CALCULATE(max(Table[Value.high]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,Day)) , blank())

  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    you can try to create a column

    Column = if( 'Table'[Value.tradingDay]-min('Table'[Value.tradingDay])<14,blank(),MAXX(FILTER('Table','Table'[Value.tradingDay]>=EARLIER('Table'[Value.tradingDay])-14 &&'Table'[Value.tradingDay]<=EARLIER('Table'[Value.tradingDay])),'Table'[Value.high]))

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Try with help from a date table

Rolling 14 = CALCULATE(max(Table[Value.high]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,Day))

 

or

 

Rolling 14 = if(CALCULATE(distinctcount(Table[Value.tradingDay]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,Day)) >=14, CALCULATE(max(Table[Value.high]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,Day)) , blank())

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi amitchandak  its not working, shoot.

for 5/1/2019 max value of last 14 days is 425 not 422.25

i tried many dax functions it is not coming.

 

pls assist.