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
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors