cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

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

4 REPLIES 4
Highlighted
Super User IV

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

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Regular Visitor

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

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...

Highlighted
Continued Contributor

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

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

Highlighted
Microsoft

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

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors