cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnbasha33
Helper III
Helper III

max value from last 14 days excluding weekends and holidays

Hi All,

 

johnbasha33_0-1610718607380.png

 

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 

2 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@johnbasha33 , 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-Calenda...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

v-kelly-msft
Community Support
Community Support

Hi @johnbasha33 ,

 

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:

v-kelly-msft_0-1610958843721.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

Hi @johnbasha33 ,

 

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:

v-kelly-msft_0-1610958843721.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

@v-kelly-msft  it works great thank you

amitchandak
Super User IV
Super User IV

@johnbasha33 , 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-Calenda...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors