Forum Discussion
Ticket Days Aging Excluding Weekends and Holidays
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!
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.
4 Replies
- Phil_Seamark
Microsoft Employee
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.
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.)
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])))
