Using a Measure to Calculate the Number of Workdays Using Conditions.

[Daniel122]

I've been struggling to create this measure. I was able to do this using a column, but was forced to change to a measure due to serious performance issues.

• Two case types distinguished by an identifier called "ID":
  • Type 1 - has a column that contains the number of work days each case has been open. (this is the easy part) 
  • Type 2 - If the case is open, then (Earliest Date - Latest Date). If closed, then (Earliest Date - Last Modified Date)
    • The way you tell if Type 2 is open or closed is using the identifier called "Status Reason"
      • If Status Reason is "Closed", "Approved", "Denied", or "Failed to Provide Additional Documentation", then closed, otherwise it is open. 
    • Need to calculate the number of work days between the two dates
• Date table contains a True/False column on whether the date is a workday or not
• The ultimate goal is to calculate the average amount of workdays in each case by month

I'm probably butchering this so any help would be greatly appreciated.

Current Measure:

Duration_Days =
VAR ID =
    MIN ( Fact[ID] )
VAR StatusReason =
    MIN ( Fact[Status Reason] )
VAR EarliestDate_Type2 =
    MIN ( Fact[Created On] )
VAR LatestOpenDate_Type2 =
    MAX ( Fact[Created On] )
VAR LatestClosedDate_Type2 =
    MAX ( Fact[Modified On] )

VAR Type1 =
    AVERAGE ( Fact[Days Open] )

VAR Type2_Open =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        ALL ( 'Date' ),
        'Date'[IsWorkingDay] = TRUE,
        'Date'[Date] >= EarliestDate_Type2,
        'Date'[Date] <= LatestOpenDate_Type2
    )

VAR Type2_Closed =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        ALL ( 'Date' ),
        'Date'[IsWorkingDay] = TRUE,
        'Date'[Date] >= EarliestDate_Type2,
        'Date'[Date] <= LatestClosedDate_Type2
    )

RETURN
    IF (
        ID = "Type1",
        Type1,
        IF (
            ID = "Type2",
            IF (
                StatusReason = "Closed"
                    || StatusReason = "Approved"
                    || StatusReason = "Denided"
                    || StatusReason = "Failed to Provide Additional Documentation",
                Type2_Closed,
                Type2_Open
            )
        )
    )

[Daniel122 · Accepted solution]

I was able to query fold this calculation into power query, which drastically improved performance. Using the Invoke Custom Function helped me rewrite the logic to create a new column in power query.

[AnthonyTilley]

Are you able to provide some sample data

[Daniel122]

The original dataset has almost 5M rows, so I reduced it down to something more manageable. Thanks very much in advance.

Sample Data Here.