Previous Working Day Measure to Incorporate Holidays

Question

I need help creating a measure that will count the total merch booked from a previous working day.

I currently have the following:

dimDate table

A. This table contains following:

i. Date Column

ii. Dayofweek column: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday

iii. Working Day column: indicates whether it is a "Working" or "Non-Working" day based on the combination of "Dayofweek" and "Holiday" Column.

Total Merch Booked measure

Here are the conditions that this previous day measure should follow:

Weekday (TODAY()) = 2 (Monday), then it should look whether the Friday before was a working day, if so, then it should calculate Total Merch booked on that day, otherwise keep repeating to the previous day to it until it finds a working day and then calculate Total Merch Booked.
Weekday(TODAY()) =1 OR Weekday (TODAY()) =7 (Saturday or Sunday), then skip and do not calculate the Total Merch booked.
Weekday(TODAY()) = any other number besides 1, 2 or 7 (Tuesday thru Friday), then it should look at the previous day to see if it is a Working day, if so, then it should calculate Total Merch booked on that day, otherwise going in backwards until it finds a working day and then calculate Total Merch booked.

I tried to use the following, but i also need to count 'holidays' in to the mix:

IF(
    WEEKDAY(TODAY()) = 2,
    CALCULATE(
        [Total Merch Booked],
        'dimDate'[Date]= (TODAY()-3)
    ),
    IF(
        WEEKDAY(TODAY()) = 1,
        BLANK(),
        IF(
            WEEKDAY(TODAY()) = 7,
            BLANK(),
            CALCULATE(
                [Total Merch Booked],
                'dimDate' [Date] = (TODAY()-1)
            )
        )
    )
)

Without describing the tables, can you add some sample data for tables with necessary columns only? And after that add your required output you wants from those sample data. This would make things easy to understand. — mkRabbani

mkRabbani mkRabbani · Accepted Answer · 2020-09-09T07:51:33

This is really difficult to suggest without sample data and expected output. But you can you try with this below measure-

total_merch_booked = 

VAR previous_working_day =
CALCULATE(
    MAX('dimDate'[Date]),
    FILTER(
        ALL('dimDate'),
        'dimDate'[Date] < TODAY()
            && 'dimDate'[Working Day] = "Working"
    )
)

RETURN
IF(
    WEEKDAY(TODAY()) IN { 1, 7 },
    BLANK(),
    CALCULATE(
        [Total Merch Booked],
        FILTER(
            ALL('dimDate'),
            'dimDate'[Date]= previous_working_day
        )
    )
)

Previous Working Day Measure to Incorporate Holidays

1 Answers