Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
2 years ago

Working Days as Filter

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,

Need Help
Show and Tell

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      ArchStanton
      Power Participant
      2 years ago

      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