Cumulative - % Monthly Rate

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!

Need Help

grantsamborn

3 years ago

Maybe try this:

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