topic Re: Calculate Comulative Average Headcount in DAX Commands and Tips

Calculate Comulative Average Headcount

Anonymous — Fri, 24 Mar 2023 21:50:14 GMT

Hi Everyone!

Sorry if my english is not good.

I want to calculate cumulative average headcount.

I have two tables

The Data_Table have the records of employees(termination date(fecha de baja [fix]), Hiredate(fecha de ingreso [fix]))

The Calendario_Table have the date (1/1/1979-12/31/2026)

I have related two fields with date table(terminationdate,hiredate)

First, I created a measure to calculate the headcount

After, I created a measure to calculate the cumulative headcount

But when I created the measure for the cumulative average, the result is not correct.

But i dont know where i am wrong.

The correct cumulative avg is

Could you tell me how I can solve it?

Thank you.

Re: Calculate Comulative Average Headcount

rautaniket0077 — Sat, 25 Mar 2023 10:29:01 GMT

That's because you have used acumulado headcount while calculating cumulative average try using Headcount instead of acumulado headcount.

if it solves your issue please accept my answer as solution.

Re: Calculate Comulative Average Headcount

Anonymous — Sun, 26 Mar 2023 15:58:13 GMT

Thanks

I changed the numerator but the result is the same

I am trying to do this operation

Re: Calculate Comulative Average Headcount

rautaniket0077 — Mon, 27 Mar 2023 06:11:17 GMT

Try replacing "Date" inside allselected with "Date[year]" because for me with similar kind of data these below 2 measures are working fine.
1)

Cummulative count =

CALCULATE(

[Order Count],

FILTER(

ALLSELECTED('Date'),

'Date'[Year] <= MAX('Date'[Year])

)

)

2)

cumulative average =

Var a = FILTER(

ALLSELECTED('Date'[Year]), ---- in your case calendario with calendario[Ano]

'Date'[Year] <= MAX('Date'[Year])

)

return DIVIDE([Cummulative count], COUNTROWS(a))

Re: Calculate Comulative Average Headcount

Anonymous — Mon, 03 Apr 2023 22:02:41 GMT

I tried the measure.The measure does work when I take the date from the same table where the data is.

But when I take the date from the calendar table, the measure doesnt work.

It should work, because is the same step,only I add the instruction userelationship to connect the tables, but it doesn´t work

Could I share the exercise with you by email?

We could review the exercise together.

Thanks!

Re: Calculate Comulative Average Headcount

rautaniket0077 — Tue, 04 Apr 2023 05:32:02 GMT

Sure please share the exercise
Email -- aniketraut0077@gmail.com