topic Re: Is there a better way to do this? in DAX Commands and Tips

Is there a better way to do this?

Casperserven1 — Thu, 23 Jun 2022 15:12:14 GMT

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"}

)

Re: Is there a better way to do this?

Anonymous — Thu, 23 Jun 2022 16:24:58 GMT

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.

Re: Is there a better way to do this?

Anonymous — Tue, 28 Jun 2022 09:31:47 GMT

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