Column that Excludes weekends from Dates

[vincetaylor]

I created a Date column that populates each row with the planned delivery date if that date is in the next 7 days. 

Deliveries in next week = IF ( AND('List of Deliveries by Planned Date'[Planned Delivery Date]>TODAY(), 'List of Deliveries by Planned Date'[Planned Delivery Date]<=TODAY()+7), 'List of Deliveries by Planned Date'[Planned Delivery Date], BLANK())

As such if today is January 26, the column will be populated with dates from Jan-26 to Feb 1. (The next 7 days)

I would like Weekends not to be included. So the next 7 days from Jan 26 should includes Dates from Jan 26 to Feb 3 excluding Jan 29 and Jan 30 (Saturday and Sunday).

Basically the next 7 working days since we don't deliver on weekends. 

Any help will be appreciated

[v-rzhou-msft · Accepted solution]

Hi @vincetaylor ,

Create a related DimDate table and then create a measure to filter your visual.

Date = 
VAR _Date =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "YearMonth",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] ),
        "Day", DAY ( [Date] ),
        "DayName", FORMAT ( [Date], "DDDD" )
    )
VAR _ADDRANK =
    ADDCOLUMNS (
        _Date,
        "RANK",
            VAR _RANK =
                RANKX (
                    FILTER ( _Date, NOT ( [DayName] IN { "Saturday", "Sunday" } ) ),
                    [Date],
                    ,
                    ASC
                )
            RETURN
                IF ( NOT ( [DayName] IN { "Saturday", "Sunday" } ), _RANK,_RANK )
    )
RETURN
    _ADDRANK

Measure:

Measure = 
VAR _DAYNAMETODAY = CALCULATE(MAX('Date'[DayName]),FILTER(ALL('Date'),'Date'[Date] = TODAY()))
VAR _RANKTODAY = CALCULATE(SUM('Date'[RANK]),FILTER(ALL('Date'),'Date'[Date] = TODAY()))
VAR _RANKADD7 = _RANKTODAY+7
VAR _CURRENTRANK = SUM('Date'[RANK])
RETURN
if(_DAYNAMETODAY IN{"Saturday","Sunday"},IF(_CURRENTRANK >=_RANKTODAY&&_CURRENTRANK<_RANKADD7&&NOT(MAX('Date'[DayName])IN{"Saturday","Sunday"}),1,0), IF(_CURRENTRANK >_RANKTODAY&&_CURRENTRANK<=_RANKADD7&&NOT(MAX('Date'[DayName])IN{"Saturday","Sunday"}),1,0))

Create a visual, add this measure in visual level filter field and set it to show items when value =1.

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.

[amitchandak]

@vincetaylor , refer if my blog on the same can help

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...