Forum Discussion
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...
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
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]))
ryan_mayu
Super User
Super UserAnonymous
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]))
ryan_mayu Thanks Ryan, it works fine.