Calculating time between a date and today - excluding weekends and holidays (need to pick dates)

[ahxl]

Hi all

I have so far been using the following formula to calculate the number of days beteween a specified date and todays date EXCLUDING weekends.

COLUMNAge = VAR StartDate = DATE(2017,1,1)
VAR EndDate = TODAY()
VAR WeekDayTable = SELECTCOLUMNS(FILTER(ADDCOLUMNS(CALENDAR(StartDate,EndDate),"WeekDay",IF(WEEKDAY([Date])in {1,7},0,1)),[WeekDay]=1),"Week Day",[Date])
VAR Result = COUNTROWS(CALCULATETABLE(filter(CROSSJOIN(WeekDayTable,'Aging Table','Data Table'),'Data Table'[Submit Date]<= [Week Day] && today()>=[Week Day]))) return Result

Works great, and I achieve the result I want to, however now I need to exclude holidays, and where I live the holidays are on different days (not according to standard calendar). Basically I need to be able to choose the dates I want to exclude from the calculation. 

Would anyone be able to provide some guidance in doing this?

Regards

Ahxl

[v-ljerr-msft]

Hi @ahxl,

It seems to me that creating an individual calendar table could be a better solution to calculate working days in Power BI currently. Here is a similar thread in which a solution is mentioned. Could you go to check if it helps in your scenario? 

Regards

[pawel1]

just wonder, would it be easier to create a calendar in Excel marking the non-standard holidays, then upload to PBI?

[parry2k]

I assume you already have a calendar table, o add new field to flag holiday in calendar table and then update your formula to exclude those dates.

[ahxl]

Hi Parry

I dont have a seperate calendar table, but I believe the above formula creates a calendar table when it is executed? The part I am struggling with is in the above formula, adding in a portion to exclude specific dates...