I am looking to return the next working day as tomorrow in a Power BI column.

[Shem]

I am looking to return the next working day as tomorrow in a Power BI column. I have the formula that works for Monday to Thursday, however, on a Friday it returns Saturday. Instead i would like Monday to be labelled as 'next working day'.

Below is the formula:

Return day = IF(DateKey[Date]=TODAY()-1,"yesterday",IF(DateKey[Date]=TODAY()+1,"next work day",IF(DateKey[Date]=TODAY(),"today",("notapplicable"))))

I am using this to filter my figures on today, next work day and also last work day.

Thanks in advance.

[Anonymous · Accepted solution]

Believe someone else must have a smart way to go.

Basically, the following nest IF means to identify if Today is Friday or Saturday, if not, go use the common +1 logic to mark Next Working day; if it is Firday, +3 as the Next Working day, if it is Saturday, + 2 as the Next Workding day.

ReturnDay = 
IF (
    FORMAT ( TODAY (), "DDDD" ) <> "Friday"
        && FORMAT ( TODAY (), "DDDD" ) <> "Saturday",
    IF (
        'DateKey'[Date]
            = TODAY () - 1,
        "Yesterday",
        IF (
            'DateKey'[Date] = TODAY (),
            "Today",
            IF ( 'DateKey'[Date] = TODAY () + 1, "Next Work Day", "Notapplicable" )
        )
    ),
    IF (
        FORMAT ( TODAY (), "DDDD" ) = "Friday",
        IF (
            'DateKey'[Date]
                = TODAY () - 1,
            "Yesterday",
            IF (
                'DateKey'[Date] = TODAY (),
                "Today",
                IF ( 'DateKey'[Date] = TODAY () + 3, "Next Work Day", "Notapplicable" )
            )
        ),
        IF (
            FORMAT ( TODAY (), "DDDD" ) = "Saturday",
            IF (
                'DateKey'[Date]
                    = TODAY () - 1,
                "Yesterday",
                IF (
                    'DateKey'[Date] = TODAY (),
                    "Today",
                    IF ( 'DateKey'[Date] = TODAY () + 2, "Next Work Day", "Notapplicable" )
                )
            )
        )
    )
)

[v-jiascu-msft]

Hi @Shem,

Can you mark the proper answer as a solution please?

Best Regards,

Dale

[Anonymous · Accepted solution]

Believe someone else must have a smart way to go.

Basically, the following nest IF means to identify if Today is Friday or Saturday, if not, go use the common +1 logic to mark Next Working day; if it is Firday, +3 as the Next Working day, if it is Saturday, + 2 as the Next Workding day.

ReturnDay = 
IF (
    FORMAT ( TODAY (), "DDDD" ) <> "Friday"
        && FORMAT ( TODAY (), "DDDD" ) <> "Saturday",
    IF (
        'DateKey'[Date]
            = TODAY () - 1,
        "Yesterday",
        IF (
            'DateKey'[Date] = TODAY (),
            "Today",
            IF ( 'DateKey'[Date] = TODAY () + 1, "Next Work Day", "Notapplicable" )
        )
    ),
    IF (
        FORMAT ( TODAY (), "DDDD" ) = "Friday",
        IF (
            'DateKey'[Date]
                = TODAY () - 1,
            "Yesterday",
            IF (
                'DateKey'[Date] = TODAY (),
                "Today",
                IF ( 'DateKey'[Date] = TODAY () + 3, "Next Work Day", "Notapplicable" )
            )
        ),
        IF (
            FORMAT ( TODAY (), "DDDD" ) = "Saturday",
            IF (
                'DateKey'[Date]
                    = TODAY () - 1,
                "Yesterday",
                IF (
                    'DateKey'[Date] = TODAY (),
                    "Today",
                    IF ( 'DateKey'[Date] = TODAY () + 2, "Next Work Day", "Notapplicable" )
                )
            )
        )
    )
)

[Shem]

Thank you sooo much for your reply. The query worked perfectly! Is there any chance you could indicate how i would alter this query so that Friday gave me the next 3 days instead of just the third day.

Sorry, a change in requirements from the powers above.

Thanks,

Shem

[Shem]

For goe this i think i got my required formula using 'OR'

[GilbertQ · Accepted solution]

Hi there

Here is the way I would do it with Variables and the SWITCH Statement

T/Y/Tom = 
VAR TodaysDate =
    TODAY ()
VAR YesterdayDate =
    TODAY () - 1
VAR NextMondaysDate =
    SWITCH(TRUE(),
        'Date'[Date] - WEEKDAY('DateKey'[Date],2)+1 >= TodaysDate, 'DateKey'[Date] - WEEKDAY('DateKey'[Date],2)+1,
        TODAY()-9
    )
VAR NextWorkingDay =
    SWITCH (
        TRUE (),
        'DateKey'[Date] = TodaysDate, "Today",
        'DateKey'[Date] = YesterdayDate, "Yesterday",
        'DateKey'[Date] = NextMondaysDate, "Next Working Day",
        "Not Applicable"
    )
RETURN
    NextWorkingDay

[Shem]

Great, thanks this seemed to work as well as the formula above.

[Anonymous]

Add another nested IF in next working day logic:

IF(TODAY()="Friday","Monday","next work day"

The above snippet will come after the underlined part.

Return day = IF(DateKey[Date]=TODAY()-1,"yesterday",IF(DateKey[Date]=TODAY()+1,"next work day",IF(DateKey[Date]=TODAY(),"today",("notapplicable"))))

Thanks

Raj