cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 @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
2 ACCEPTED SOLUTIONS
Super User

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

Super 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]))``

Proud to be a Super User!

5 REPLIES 5
Super 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]))``

Proud to be a Super User!

Anonymous
Not applicable

@ryan_mayu Thanks Ryan, it works fine.

Super User

@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
Not applicable

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
Not applicable

@amitchandak  Thanks Amit it works fine.