Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 can you share tickets sample data in excel thru google drive or other means and I will get back to you with solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |