max value from last 14 days

[Anonymous]

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 tell me how to get that? i tried different dax but not working.  @MFelix @Kinjal @simrantuli @Pragati11 @mwegener @amitchandak 

Value.tradingDay	Value.high	Max Value
12/14/2018	415.75
12/17/2018	417.75
12/18/2018	421.25
12/19/2018	421.25
12/20/2018	418.5
12/21/2018	418.25
12/24/2018	417.75
12/26/2018	416
12/27/2018	416.75
12/28/2018	418
12/31/2018	419.25
1/2/2019	419.5
1/3/2019	421
1/4/2019	422.5	422.5
1/7/2019	422.75	422.75
1/8/2019	421.75	422.75
1/9/2019	421.75	422.75
1/10/2019	419	422.75
1/11/2019	423.25	423.75
1/14/2019	420.5

[amitchandak · Accepted solution]

@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 · Accepted solution]

@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]))

[Anonymous]

@ryan_mayu Thanks Ryan, it works fine.

[amitchandak · Accepted solution]

@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]

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.

[Anonymous]

@amitchandak  Thanks Amit it works fine.