Is there a better way to do this?

[Casperserven1]

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-yiruan-msft · Accepted solution]

Hi @Casperserven1 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

You can create a holiday table with these holiday date(20/12/2021,27/12/2021...) first.

Then update the formula of your measure as below(the part with red font is updated one):

1_EmployeeCount =
CALCULATE (
    SUM ( CumulativeData[Person Count] ),
    FILTER (
        'CalCumulative',
        NOT ( 'CalCumulative'[WeekStarting] ) IN VALUES ( 'Holidays'[Holidays] )
    ),
    FILTER (
        CumulativeData,
        CumulativeData[Employee Type] IN { "Employee", "Ex-employee" }
    )
)

If you don't want to create any holiday table, you can update the formula of your measure as below. One problem here is that it's not easy to maintain later, if new holiday dates are added or changed, you need to find the related measures first before modifying 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" )
            }
    ),
    FILTER (
        CumulativeData,
        CumulativeData[Employee Type] IN { "Employee", "Ex-employee" }
    )
)

Best Regards

[sathya_s]

Hi @Casperserven1, 

Is there any pattern/logic in the mentioned dates. Are you calculating the expression for filtering out just weekends or are there specific dates that are holidays.