cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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.






Did I answer your question? Mark my post as a solution.

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
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
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? Smiley Happy

 

Regards

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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

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