Working Days as Filter

[ArchStanton]

Hi,

I have the following code that calculates the Total Days duration of X.

Case Length (Adj) = 
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        DAY
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        DAY
    )
)

However, I recently added a Working Day calculated column in my Date calendar which shows TRUE /  FALSE if the Date is a Working Day or not - the Code is below:

Working Days = 
NOT WEEKDAY ( Date2[Date] )
    IN { 1, 7 } && ISBLANK(  COUNTROWS ( RELATEDTABLE ( 'bank-holidays' ) ) )

Is there a way I can use this Working Days Calculated column in the Case Length (Adj) Measure so it removes the non working days from the final result?

I tried to simply add it to a Matrix visual but it didn't work because it reduced the no of items I am trying to average.

Without Working Day Filter:

With Working Day Filter added - notice the total is now 1649, it should still be 2303

Thanks,

[Anonymous]

Hi  @ArchStanton ,

If you want the Total row to not change with it, you can create a dummy table and then use IF+ HASONEFILTER() to determine if the Total

Measure 2 =
var _table=
SUMMARIZE(
    ALL('Cases'),[Team Name],"Value1",SUMX('Cases',[Active Cases]))
return
IF(
    HASONEFILTER('Cases'[Team Name]),
    [Case Length (Adj)],
SUMX(
    _table,[Value1]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArchStanton]

Thanks for this, unfortunately it doesn't work because Case Length [Adj] is a Calculated Column within the Cases table.

Instead, Is it possible to SUM all the non-working days in my Date2 table and subtract this total from the Case Length Average for each Team?

Thanks