Display expected value of a key measure daily excluding Weekends & Public Holidays in Power BI

0
votes

There are two main measures displayed on a Power BI Report. One is the Actual value (team hour stats) and the other one is the expected value. The data is 'T-1' where 'T' is today. The team works Sun-Thurs. The expected value is currently being displayed daily using the below measure:

Expected Overall % = CALCULATE((DAY(TODAY())-1)/(VAR t = TODAY() RETURN
COUNTROWS( CALENDAR( FORMAT(t,"YYYY-MM-01") , EDATE(FORMAT(t,"YYYY-MM-01"),1)-1 ) ))*100)

However, I am encountering the following issues:

  • It does not exclude weekends and public holidays. Since the team work only during the weekdays, how can the expected value pause during weekends and public holidays automatically?
  • The Actual value has a date slicer which gives values based on months, weeks etc. How can we also relate expected value along with it? Is this possible?

Note - I was able to create a custom date column with weekdays/weekends and public holidays.

1
datepowerbidax

1 Answers

0
votes

If you have Calendar table, you can use the function WEEKDAY() to check what day it is. Default 1 = Sunday; There is no function for a public holidays, but if you have a column for this information, you can exclude it as well.

for example:

calculate(countrows( CALCULATETABLE('Calendar', not(WEEKDAY('Calendar'[Date]) in {1,7} ), 'Calendar'[PublicHoliday] = 0 )