ANJIR

Exclude Weekend and Holidays in Power BI Report

—

by

nealanjir
in
  1. Create a SharePoint list called HolidayList with a Date column and populate with all the holidays
  2. Connect to the SharePoint list in your Power BI repot
  3. Create a custom column on your original list and paste the code below:
_CustomColumnName = 
VAR StartDate = DataSource[StartDateColumn]
VAR EndDate=DataSource[EndDateColumn]
VAR Holidays=FILTER(HolidayList,HolidayList[Date]>=StartDate && HolidayList[Date]<=EndDate)
RETURN
IF(
    NOT(ISBLANK(StartDate) || ISBLANK(EndDate)) && StartDate<=EndDate,
    COUNTX(
        FILTER(
            ADDCOLUMNS(
                CALENDAR(StartDate,EndDate),
                "DayType",WEEKDAY([Date],2)
            ),
            [DayType]<=5 && NOT CONTAINS(Holidays,HolidayList[Date],[Date]) 
        ),
            [Date]
        ),
    BLANK()
)