cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

DATEDIFF with a filter

Hi,

 

I need to calculate several TATs for my report, but only having number of business days in those TATs.

 

For example, I have a CASE OPEN date and a CASE CLOSE date, I need to calculate the CASE TAT being a Datediff between them, but  returning only business days.

I already have a Date table with all dates marked as "weekday" and "weekend", holidays included.

 

Is there a way to somehow apply a filter to Datediff, or use any other function(s) to have the desired result?

I don't need a total of working days, I need number of days between certain dates to track the service event (opened to part order, part delivery to repair, repair to closure - all in nuber of working days between those dates).

 

please help, thanks!

1 REPLY 1
Super User II
Super User II

Hi @Ilona , try this measure:

Business Days from Part Delivery to Repair Date = 
VAR vStartDate =
    MAX ( ServiceEvent[Part Delivery Date] )
VAR vEndDate =
    MAX ( ServiceEvent[Repair Date] )
VAR vBusinessDays =
    FILTER (
        'Date',
        'Date'[Date] >= vStartDate
            && 'Date'[Date] <= vEndDate
            && 'Date'[Is Business Day] = "Y"
    )
VAR vResult =
    COUNTROWS ( vBusinessDays )
RETURN
    vResult 

 

This solution requires the column 'Date'[Is Business Day].

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors