Forum Discussion

gpauli200's avatar
gpauli200
Frequent Visitor
8 years ago
Solved

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 Da...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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's avatar
gpauli200
Frequent Visitor
8 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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])))