cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver IV
Resolver IV

Using date table with weekend and bank holidays to accurately measure "ticket open" day KPIs

Hi all,

 

Bit of a tricky one here.

 

I've created a date table from 2019-2021, which includes a Y/Blank on working days so I can figure out the total days a ticket is open, by minusing two dates against each other. To make this accurate, I need to use this date table to not count the weekends and bank holidays in the year. 

 

So a rough idea of what I'm trying to achieve will be:

 

 "Complaint Received" (complaints table) minus "Final Response Sent" (complaints table), ignoring blanks in "Working Day" (date table). 

 

image.png

Date table featuring weekend and bank holiday info^ 

 

image.png

Complaints recieved column and table (underlined) needed for DAX^

 

image.png

"Final Response Sent" column and table (underlined) needed for DAX ^ 

 

Kind regards,

 

Jordan 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Using date table with weekend and bank holidays to accurately measure "ticket open" da

@jme1calff 

You can add it as a calculated column to your complaints table like so.

Working Days =
        CALCULATE (
            COUNTROWS ( DateTable ),
            DATESBETWEEN ( DateTable[Date], 'MGA - Complaints (SCANS)[Complaint Received], 'MGA - Complaints (SCANS)[Finla Response Sent] ),
            DATES[Working Day] = 'Y'
        ) -1

The pattern is from this article: https://www.sqlbi.com/articles/counting-working-days-in-dax/ 

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Using date table with weekend and bank holidays to accurately measure "ticket open" da

@jme1calff , for open tickets, referhttps://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

For working day calculation refer the second Page

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Using date table with weekend and bank holidays to accurately measure "ticket open" da

@jme1calff 

You can add it as a calculated column to your complaints table like so.

Working Days =
        CALCULATE (
            COUNTROWS ( DateTable ),
            DATESBETWEEN ( DateTable[Date], 'MGA - Complaints (SCANS)[Complaint Received], 'MGA - Complaints (SCANS)[Finla Response Sent] ),
            DATES[Working Day] = 'Y'
        ) -1

The pattern is from this article: https://www.sqlbi.com/articles/counting-working-days-in-dax/ 

View solution in original post

Highlighted
Resolver IV
Resolver IV

Re: Using date table with weekend and bank holidays to accurately measure "ticket open" da

Thank you both! @jdbuchanan71 @amitchandak 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors