cancel
Showing results for
Did you mean:
Highlighted
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).

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

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

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``````

3 REPLIES 3
Highlighted
Super User IX

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

For working day calculation refer the second Page

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

Proud to be a Super User!

Highlighted
Super User IV

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

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``````

Highlighted
Resolver IV

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

Thank you both! @jdbuchanan71 @amitchandak

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
Top Kudoed Authors