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

Duration and null dates relative to today, excluding weekends

Hi all,

 

Context: Cases must be acknowledged within 2 business days.

Criteria 1: If a case has been open for more than 2 days with no acknowledge date, it is overdue.

Criteria 2: If a case has an acknowldge date of +2 days from the created date, it is overdue.

So I would like to count all cases where the acknowledge date is either +2 days from the created date, or the created date is +2days old and the acknowledge date is blank. The count must also exclude weekends.

 

Attempt:

Criteria 1: I tried adding a conditional column in Power Query where each if [Age] >2 and [Local Ack Date] = null then 1 else 0 but this does not account for weekends

 

Critera 2: This DAX formula counts the business days between Created and Acknowledged using a secondary date table, but if there is no "Acknowledged Date", the field is blank

 

Days to Ack =
SUMX (
    FILTER (
        'Date',
        'Date'[Date]>= 'CRM Cases All'[Local Created Date]
            && 'Date'[Date]<= 'CRM Cases All'[Local Ack Date]
    ),
    'Date'[AddBusinessDayInd]
)
 
Need: So I'm looking for help solving Criteria 1, identify cases where the created date is older than 2 days (not including today, excluding weekends) with no acknowledge date. Would love any ideas 🙂
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could create a calculated column like

Is overdue =
ISBLANK ( 'CRM Cases All'[Local Ack Date] )
    || NETWORKDAYS (
        'CRM Cases All'[Local Created Date],
        'CRM Cases All'[Local Ack Date]
    ) > 2

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

You could create a calculated column like

Is overdue =
ISBLANK ( 'CRM Cases All'[Local Ack Date] )
    || NETWORKDAYS (
        'CRM Cases All'[Local Created Date],
        'CRM Cases All'[Local Ack Date]
    ) > 2
vww
Frequent Visitor

Hi @johnt75 thanks so much, this has solved it. I'm new to DAX and didn't realise there was an "or" expression ( || ). Thanks a lot!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors