Cumulative - % Monthly Rate

[uie07923]

Hello all,

I'm struggling to reach the cumulative fluctuation rate.

Every month end, we extract a headcount total.

During that month there are employee exits. The rate between Exits / Total Headcount is the fluctuation rate for that month.

I need to calculate the result for each month and add to the previous one to reach the Cumulative Fluctuation.

In the attached excel is the example and in the pbix, the databases.

https://1drv.ms/u/s!AqxrG8wgr5EolClOUAAwrlrWmfya?e=BQ424a

Monthy Headcount db:

MONTH	EMPLOYEE ID
jan-22	1
jan-22	2
jan-22	3
jan-22	4
jan-22	5
jan-22	6
jan-22	7
jan-22	8
jan-22	9
jan-22	10
fev-22	1
fev-22	2
fev-22	3
fev-22	4
fev-22	5
fev-22	6
fev-22	7
fev-22	8
fev-22	9
fev-22	10
mar-22	1
mar-22	2
mar-22	3
mar-22	4
mar-22	5
mar-22	6
mar-22	7
mar-22	9
mar-22	10

Exits db:

Exit Date	Emp. ID
15/mar	8
02/abr	4
25/abr	5
jul-22	10
dez-22	1

Calculation

Thanks in advance!

[amitchandak]

@uie07923 , Assuming you have common date table with month year and measure fluctuation rate

Try a new measure

fluctuation rate Cumm= sumx(Values(Date[Month Year]), [fluctuation rate])

[uie07923]

Are you considering the creation of an additional table with only the results of the fluctuation rate?

I tried the measure, but it didn't work:

[grantsamborn · Accepted solution]

Maybe try this:

fluctuation rate Cumm = 
    SUMX(
        FILTER(
            ALL( dCalendar[YearMonth] ),
            dCalendar[YearMonth] <= MAX( 'dCalendar'[YearMonth] )
        ),
        [fluctuation]
    )

[aj1973]

Hi @uie07923 

Is this what you want to see