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] )
    )
)

One thought on “Power BI & DAX: Working with Workdays

  1. Hi Andrea,

    Great job, what was not working for me was Working days left…there was no result…
    Could it be because of MonthID wrong format?

    Thanks

    Peter

Leave a Reply to Peter Svec Cancel 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 )

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