Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
ayush_sinha
Helper I
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 imagerefer to image

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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

View solution in original post

v-rzhou-msft
Community Support
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 =
    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 )

1.png

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.

 

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
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 =
    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 )

1.png

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.

 

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
Super User
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

can you please attach your report ?

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors