Ticket Days Aging Excluding Weekends and Holidays

[gpauli200]

I am having trouble showing the age of open tickets while exclusing weekends and holidays.

I have my fact table, a related datedim table, and a holiday table. I also have a column for "Is Work Day" = IF(OR(dCalendar[WeekDayNumber]=1,dCalendar[WeekDayNumber]=7),0,IF(ISBLANK(dCalendar[Holiday]),1,0)) in my datedim table excluding weekends and holidays.

In my fact table I have a custom column for "Days Aging" =  DATEDIFF(Oracle[Create Date EST].[Date],NOW(),DAY) for all open tickets. Currently this is displaying the day date difference with weekends and holidays included. How do I show the age of a ticket excluding weekends and holidays? I'm not sure how to tie this all together.

Thank you!

[parry2k]

@gpauli200 can you share tickets sample data in excel thru google drive or other means and I will get back to you with solution.

[Phil_Seamark · Accepted solution]

Hi @gpauli200

One option is to add a column to your Date table that carrys a 1 for working days and a 0 for weekends and holidays.  Then simply sum that column for the rows between the start & end of your ticket (for each ticket).  This can be done either as a calculated column, measure or as a calculated table.

[gpauli200]

Thank you for the quick response! I currently have a column in my date table with 1 as working day, 0 as non-working day.

I think I figured out the correct formula per your tip, and I will share it below. It appears to be working correctly.

BUSINESS DAYS AGING = CALCULATE(SUM(dCalendar[IfWorkDay]),DATESBETWEEN(dCalendar[Date],Oracle[Create Date EST],NOW()))-1

(I subtracted 1 because I think it was counting the create date as an aging day.)

[Anonymous]

@gpauli200  Thank you!  Every other solution involved a Calculate function that caused a 3 to 6 minute table reload time for me. (This one does it in about 1.5 seconds!)

I did run into an issue with the '-1' making the orders completed prior to the due date show a Days Late of -1, so i added a +1 to the initial date to push it past the initial CalDate. Seems to work fine.

BUSINESS DAYS LATE = CALCULATE(SUM('CalDate'[IsWorkDay]),DATESBETWEEN('CalDate'[Date],min(OPSplanning[ReqShipDate])+1,MAX(OPSplanning[InvoiceDate])))