Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
and01
New Member

Calculate Comulative Average Headcount

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)

 

and01_8-1679692960582.png

 

First, I created a measure to calculate the headcount

and01_9-1679693229980.png

 

After, I created a measure to calculate the cumulative headcount 

and01_1-1679691447572.png

and01_5-1679692119370.png

 

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

But i dont know  where i am wrong.

 

and01_7-1679692379547.png

 

and01_6-1679692337555.png

The correct cumulative avg is

 

and01_12-1679693479303.png

 

Could you tell me how I can solve it?

Thank you.

 

 

5 REPLIES 5
rautaniket0077
Resolver I
Resolver I

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


rautaniket0077_0-1679897411315.png

 




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!

 

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

rautaniket0077
Resolver I
Resolver I

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.

Hi

Thanks

I changed the numerator but the result is the same

 

and01_1-1679844987483.png

I am trying to do this operation

 

and01_3-1679846275845.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors