Showing 5 weekdays data from the selected date

[Anonymous]

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

[amitchandak · Accepted solution]

@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

[v-rzhou-msft · Accepted solution]

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.

[Anonymous]

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.

[amitchandak · Accepted solution]

@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

[Anonymous]

can you please attach your report ?

[amitchandak]

@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.