cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gpauli200 Frequent Visitor
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!

 

Aging INCLUDES Weekends and Holidays.PNGRelationships to Oracle FactTable.png

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Ticket Days Aging Excluding Weekends and Holidays

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Microsoft Phil_Seamark
Microsoft

Re: Ticket Days Aging Excluding Weekends and Holidays

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Super User IV
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.






Did I answer your question? Mark my post as a solution.

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





gpauli200 Frequent Visitor
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.)

Capture.PNG

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

 

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

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?

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.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors