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] > 5FALSE ()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] )
    )
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s