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 tell me how to get that? i tried different dax but not working. MFelix Anonymous 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 |
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]))
5 Replies
ryan_mayuSuper User
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 Thanks Ryan, it works fine.
- amitchandak
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())
amitchandak Thanks Amit it works fine.
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.