cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Ticket Days Aging Excluding Weekends and Holidays

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.

Proud to be a Datanaut!

4 REPLIES 4
Microsoft

## Re: Ticket Days Aging Excluding Weekends and Holidays

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.

Proud to be a Datanaut!

Super User IV

## Re: Ticket Days Aging Excluding Weekends and Holidays

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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Ticket Days Aging Excluding Weekends and Holidays

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
Not applicable

## Re: Ticket Days Aging Excluding Weekends and Holidays

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

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.