topic Average Count in DAX Commands and Tips

Average Count

DH3612 — Tue, 21 Nov 2023 11:27:20 GMT

Hi folks. People Have been amazing so far helping me understand what to do. Hoping we can get this next bit running ok.

I work out Current EMployees using this piece of code which works great:

Current Employees = CALCULATE(COUNTx(FILTER(STAFFMASTER,STAFFMASTER[Current Employment: Start Date]<=max('Date'[Date]) && (ISBLANK(staffmaster[End Date]) || staffmaster[End Date]>max('Date'[Date]))),(STAFFMASTER[Unique Id])),CROSSFILTER(STAFFMASTER[Current Employment: Start Date],'Date'[Date],None))

Then I work out Employees at a specific date using this piece:

Employed Staff Count = var minD = MIN('Date'[Date]) var maxD = MAX('Date'[Date]) RETURN CALCULATE( DISTINCTCOUNT('STAFFMASTER'[Unique ID]), 'STAFFMASTER'[Current Employment: Start Date] <= minD && ( 'STAFFMASTER'[End Date] >= maxD || ISBLANK('STAFFMASTER'[End Date]) ), CROSSFILTER('Date'[Date], STAFFMASTER[Current Employment: Start Date], None) )

If I try to do a simple measure for example of

Average Staff = ([current employees]+[Employed Staff Count])/2

then I get a wrong answer because the measure doesnt have the same filters active as the measures in those code snippets.

If I then try and resolve this by doing a measure like this -

Average Staff = VAR CurrentEmps = CALCULATE(COUNTx(FILTER(STAFFMASTER,STAFFMASTER[Current Employment: Start Date]<=max('Date'[Date]) && (ISBLANK(staffmaster[End Date]) || staffmaster[End Date]>max('Date'[Date]))),(STAFFMASTER[Unique Id])),CROSSFILTER(STAFFMASTER[Current Employment: Start Date],'Date'[Date],None)) VAR Employed = var minD = MIN('Date'[Date]) var maxD = MAX('Date'[Date]) RETURN CALCULATE( DISTINCTCOUNT('STAFFMASTER'[Unique ID]), 'STAFFMASTER'[Current Employment: Start Date] <= minD && ( 'STAFFMASTER'[End Date] >= maxD || ISBLANK('STAFFMASTER'[End Date]) ), CROSSFILTER('Date'[Date], STAFFMASTER[Current Employment: Start Date], None) ) RETURN (CurrentEmps + Employed) / 2

I get an incorrect answer.

Any suggestions how to work this or even a simpler solution.

Basically the sum needs to be current staff+staff at a specific date / 2 which will give the average staff over that period.

Re: Average Count

fbura — Tue, 21 Nov 2023 13:30:26 GMT

Hi, without investigating much about the logics about the calculation of 'Employed' and 'CurrentEmps' i assume your DAX formula will result not in an incorrect answer but in an error due to the fact you put two RETURN statement inside one DAX formula. Before thinking about the quality of your calculation i would try to run this instead:

VAR CurrentEmps =
CALCULATE (
COUNTX (
FILTER (
STAFFMASTER,
STAFFMASTER[Current Employment: Start Date] <= MAX ( 'Date'[Date] )
&& (
ISBLANK ( staffmaster[End Date] )
|| staffmaster[End Date] > MAX ( 'Date'[Date] )
)
),
( STAFFMASTER[Unique Id] )
),
CROSSFILTER ( STAFFMASTER[Current Employment: Start Date], 'Date'[Date], NONE )
)
VAR Employed =
CALCULATE (
DISTINCTCOUNT ( 'STAFFMASTER'[Unique ID] ),
'STAFFMASTER'[Current Employment: Start Date] <= MIN ( 'Date'[Date] )
&& (
'STAFFMASTER'[End Date] >= MAX ( 'Date'[Date] )
|| ISBLANK ( 'STAFFMASTER'[End Date] )
),
CROSSFILTER ( 'Date'[Date], STAFFMASTER[Current Employment: Start Date], NONE )
)
RETURN
( CurrentEmps + Employed ) / 2

Logics are the same as yours but in my opinion minD and maxD variables were not necessary.

Hope this will help you! Cheers!

Re: Average Count

DH3612 — Tue, 21 Nov 2023 13:49:44 GMT

Thanks,

I amended to use the code you provided, but get the same answer.

in the example I am using, I have the date filtered to 31/10/22, and the staff count there is 1789 (Employed Staff Count). The staff count today is 1996 (Current Staff). So adding those together then dividing by two should be 1892.5. But the result seems to be 1788.