max value from last 14 days excluding weekends and holidays

[Anonymous]

Hi All,

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

I have tried many dax functions that is not coming.

kindly assist.

@amitchandak @Pragati11 @mwegener 

[amitchandak · Accepted solution]

@Anonymous , Need columns like this in date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

a measure like

CALCULATE(Max('Table'[Value]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Work Date cont Rank])-14 && 'Date'[Week Rank]<=max('Date'[Work Date cont Rank])))

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First go to query editor>create an index column;

Then create a measure as below:

Measure = 
CALCULATE(MAX('Table'[Value.high]),FILTER(ALL('Table'),'Table'[Index]>=MAX('Table'[Index])-14&&'Table'[Value.tradingDay]<MAX('Table'[Value.tradingDay])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft  it works great thank you

[amitchandak · Accepted solution]

@Anonymous , Need columns like this in date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

a measure like

CALCULATE(Max('Table'[Value]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Work Date cont Rank])-14 && 'Date'[Week Rank]<=max('Date'[Work Date cont Rank])))

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

[Anonymous]

@amitchandak  where is week rank column ?? it is missing from the measure