filter out dates - a better way

[Casperserven1]

is there a better way to do this?

1_EmployeeCount = -- count the number of Employees in filtering out holiday dates

CALCULATE(

SUM( CumulativeData[Person Count] ) ,

FILTER(CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "20/12/2021" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "27/12/2021" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "17/01/2022" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "03/05/2021" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "31/05/2021" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "11/04/2022" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "30/08/2021" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "18/04/2022" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "02/05/2022" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "23/05/2022" )),

FILTER( CalCumulative,CalCumulative[WeekStarting]<> DATEVALUE( "03/01/2022" )),

FILTER (CumulativeData,CumulativeData[Employee Type] IN {"Employee","Ex-employee"}

)

)

[v-yalanwu-msft · Accepted solution]

Hi, @Casperserven1 ;

You could try it.

1_EmployeeCount =
-- count the number of Employees in filtering out holiday dates
CALCULATE (
    SUM ( CumulativeData[Person Count] ),
    FILTER (
        CalCumulative,
        NOT ( CalCumulative[WeekStarting] )
            IN {
                DATEVALUE ( "20/12/2021" ),
                DATEVALUE ( "27/12/2021" ),
                DATEVALUE ( "17/01/2022" ),
                DATEVALUE ( "03/05/2021" ),
                DATEVALUE ( "31/05/2021" ),
                DATEVALUE ( "11/04/2022" ),
                DATEVALUE ( "30/08/2021" ),
                DATEVALUE ( "18/04/2022" ),
                DATEVALUE ( "02/05/2022" ),
                DATEVALUE ( "23/05/2022" ),
                DATEVALUE ( "03/01/2022" )
            }
                && CumulativeData[Employee Type] IN { "Employee", "Ex-employee" }
    )
)

and if you have more logic about datevalue, could you share it?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.