cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Showing 5 weekdays data from the selected date

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.refer to image

2 ACCEPTED SOLUTIONS
Super User

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

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

Community Support

Hi @ayush_sinha ,

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 =
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekDay", WEEKDAY ( [Date] )
)
_Date,
"Rank",
IF (
[WeekDay] IN { 6, 7 },
BLANK (),
RANKX ( FILTER ( _Date, NOT ( [WeekDay] IN { 6, 7 } ) ), [Date],, ASC )
)
)
RETURN

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.

5 REPLIES 5
Community Support

Hi @ayush_sinha ,

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 =
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekDay", WEEKDAY ( [Date] )
)
_Date,
"Rank",
IF (
[WeekDay] IN { 6, 7 },
BLANK (),
RANKX ( FILTER ( _Date, NOT ( [WeekDay] IN { 6, 7 } ) ), [Date],, ASC )
)
)
RETURN

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.

Helper I

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.

Super User

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

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

Helper I

Super User

@ayush_sinha , This blog has table

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors