Turnover rate with a balance table

Mike44 — Sun, 06 Sep 2020 08:17:05 GMT

Hello community,

I've got a balance table with staff log, like the following stricture:

Employee name
Date
Country
Department
Position
Balance (-1 if the employee was dismissed, +1 if hired)

The data structure allows showing the headcount dynamics as a columns chart using country, department, position as dimensions. In addition, we calculate the turnover rate by the classical formula (begin+end)/2 / # of dismissed. All works fine until users select a particular period (e.g., clicking on the chart where the Month/Year dimension is used as an X-axis.

The problem is in calculating the begin qty which requires to take sum of all Balance values considering all selections except for the Year/Date, but before the current dimension value in the chart. e.g., when the chart shows data by a month/year every month will have different begin qty which should be considered.

I understand I should use a compound set expression for this measure, but your advice would save me a lot of time.

Many thanks in advance!

topic Turnover rate with a balance table in App Development

Turnover rate with a balance table