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 this video. Please watch the video to understand the theory behind the calculations. I am pasting the calcs below just in case you find them useful.

Link to my blog: https://businessintelligist.com/?p=2580

DATE Table calcs (due to Youtube limitations I had to use < for less than sign and > for greater than, so you will have to fix that for some functions for them to work):

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

Видео Power BI & DAX: Working with Workdays канала KnowledgeBank by obviEnce