Forum Discussion

ahxl's avatar
ahxl
Regular Visitor
8 years ago

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

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      ahxl
      Regular Visitor
      8 years ago

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

  • pawel1's avatar
    pawel1
    Icon for Kudo Kingpin rankKudo Kingpin
    8 years ago

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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? :smileyhappy:

     

    Regards