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.
