Power BI & DAX: Working with Workdays

Working with Workdays does not have to be hard work. At least that’s the claim I make in the video below:

Please watch the video to understand the theory behind the calculations. I am pasting the calcs below just in case you find them useful.

DATE Table calcs:

IsHoliday =
VAR Holiday =
    RELATED ( Holidays[Holiday] )
RETURN
    IF ( Holiday = BLANK (), FALSE (), TRUE () )

WeekDayNo =
WEEKDAY ( ‘Date'[Date], 2 )

IsWorkDay =
IF ( ‘Date'[IsHoliday] || ‘Date'[WeekDayNo] > 5, FALSE (), TRUE () )

WorkDayID =
IF (
    ‘Date'[IsWorkDay],
    CALCULATE (
        COUNT ( ‘Date'[Date] ),
        FILTER (
            ALL ( ‘Date’ ),
            ‘Date'[IsWorkDay] = TRUE ()
                && ‘Date'[Date] <= EARLIER ( ‘Date'[Date] )
        )
    )
)

Measures:

NoOfWorkDays =
CALCULATE ( COUNT ( ‘Date'[Date] ), ‘Date'[IsWorkDay] )

Revenue by Work Day =
DIVIDE ( [Revenue], [NoOfWorkDays] )

Revenue by Work Day MoM % =
VAR PriorMonthRevByWorkDay =
    CALCULATE ( [Revenue by Work Day], PREVIOUSMONTH ( ‘Date'[Date] ) )
RETURN
    DIVIDE (
        [Revenue by Work Day] – PriorMonthRevByWorkDay,
        PriorMonthRevByWorkDay
    )

WorkDaysLeftInMonth =
CALCULATE (
    [NoOfWorkDays],
    FILTER (
        ALL ( ‘Date’ ),
        ‘Date'[MonthID] = SELECTEDVALUE ( ‘Date'[MonthID] )
            && ‘Date'[WorkDayID] > SELECTEDVALUE ( ‘Date'[WorkDayID] )
    )
)

WorkDaysLeftInMonth =
CALCULATE (
    [NoOfWorkDays],
    FILTER (
        ALL ( ‘Date’ ),
        ‘Date'[MonthID] = SELECTEDVALUE ( ‘Date'[MonthID] )
            && ‘Date'[WorkDayID] > SELECTEDVALUE ( ‘Date'[WorkDayID] )
    )
)