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

[Anonymous]

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

Date table featuring weekend and bank holiday info^ 

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

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

Kind regards,

Jordan 

[Anonymous]

Thank you both! @jdbuchanan71 @amitchandak 

[jdbuchanan71 · Accepted solution]

@Anonymous 

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/ 

[amitchandak]

@Anonymous , for open tickets, referhttps://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

For working day calculation refer the second Page

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