Calculate Comulative Average Headcount

[and01]

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.

[rautaniket0077]

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))

[and01]

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!

[rautaniket0077]

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

[rautaniket0077]

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.

[and01]

Hi

Thanks

I changed the numerator but the result is the same

I am trying to do this operation