topic Showing 5 weekdays data from the selected date in DAX Commands and Tips

Showing 5 weekdays data from the selected date

Anonymous — Thu, 23 Dec 2021 07:57:47 GMT

I want to display the data of 5 working days( excluding weekends) from the selected date . For example if I select 16-12-2021 from the slicer it should show me 5 working days data including the selected date. Since 16th is Thursday it should show me data for 16th, 17th,20th, 21st and 22nd december data as 18 and 19 are weekends. Hope you understand my point.

Re: Showing 5 weekdays data from the selected date

amitchandak — Thu, 23 Dec 2021 08:26:19 GMT

Anonymous , refer to my blog, traveling across weekdays.

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

But when you select a date and you need more than date you need an independent date table too

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

example measure

///Date1 is independent Date table, Date is joined with Table
new measure =
var _min = maxx(allselected(Date1),Date1[Work Date cont Rank])
var _max = _min +5
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Work Date cont Rank] >=_min && 'Date'[Work Date cont Rank] <=_max))

columns

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)

if needed add workday filter in measure

Re: Showing 5 weekdays data from the selected date

Anonymous — Thu, 23 Dec 2021 08:35:10 GMT

can you please attach your report ?

Re: Showing 5 weekdays data from the selected date

amitchandak — Thu, 23 Dec 2021 08:57:01 GMT

Anonymous , This blog has table

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

Other formulas I suggested, based on independent and dependent/joined date table

If these do not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Showing 5 weekdays data from the selected date

Anonymous — Mon, 27 Dec 2021 06:50:23 GMT

Hi Anonymous ,

Here I suggest you to create an unrelated date table to create the date slicer. I add a rank column which sort all dates except weekends in my date table. Here is my code.

Date = VAR _Date = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "WeekDay", WEEKDAY ( [Date] ) ) VAR _ADDRank = ADDCOLUMNS ( _Date, "Rank", IF ( [WeekDay] IN { 6, 7 }, BLANK (), RANKX ( FILTER ( _Date, NOT ( [WeekDay] IN { 6, 7 } ) ), [Date],, ASC ) ) ) RETURN _ADDRank

Then create a measure ,then add this measure into the filter field in your matrix visual and set it to show items when the value =1.

Filter = VAR _SELECTRANK = SELECTEDVALUE ( 'Date'[Rank] ) VAR _DATELIST = CALCULATETABLE ( VALUES ( 'Date'[Date] ), FILTER ( ALL ( 'Date' ), 'Date'[Rank] >= _SELECTRANK && 'Date'[Rank] <= _SELECTRANK + 4 ) ) RETURN IF ( MAX ( 'Table'[Date] ) IN _DATELIST, 1, 0 )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Showing 5 weekdays data from the selected date

Anonymous — Tue, 04 Jan 2022 10:05:55 GMT

Without filtering the slicer can we show 5days result like if we select a non working day from slicer it should the next 5 working days data. Instaed of filtering out the non working days from slicer try to display coming 5 working days. for example - if 25 december is holiday show the 5 days data from the next working day of december 25.