Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Maybe try this:
fluctuation rate Cumm =
SUMX(
FILTER(
ALL( dCalendar[YearMonth] ),
dCalendar[YearMonth] <= MAX( 'dCalendar'[YearMonth] )
),
[fluctuation]
)
@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])
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:
Maybe try this:
fluctuation rate Cumm =
SUMX(
FILTER(
ALL( dCalendar[YearMonth] ),
dCalendar[YearMonth] <= MAX( 'dCalendar'[YearMonth] )
),
[fluctuation]
)
Hi @uie07923
Is this what you want to see
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |