Forum Discussion

vincetaylor's avatar
vincetaylor
Helper I
4 years ago
Solved

Column that Excludes weekends from Dates

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.