Sum of the current month and last month in a monthly-wise table

[BoXiangWang]

Hi all,

I want to calculate turnover rate per month and here is the formula:

Current month's turnover

/

[(Current month's end headcount + Last month's end headcount) / 2]

However, I need do display the result in a table and line chart.

Due to the Monthly restriction, the expression seems not as easy as I expected.

I don't have a master calendar and I'd like to ask for help. How can I achieve this?

For example, in May, the denominator should be (119+120)/2;

in April, the denominator should be (120+118)/2

I cannot use the following expression because it causes a wrong result. It actually add the number per month.

For instance, in May it results in (119+0)/2;

in April, it shows (120+120)/2, which is not what I want.

(
(
(count(distinct {1<%headcount_key={"*"}, [is_seconded]={"0"}, [section_code] -= {"VAC", "CPP"}, [date_month]={">=$(=MonthStart(vStartDate))<=$(=MonthEnd(vEndDate))"}>} [dim_ppl_employee_sk]))
+
(count(distinct {1<%headcount_key={"*"}, [is_seconded]={"0"}, [section_code] -= {"VAC", "CPP"}, [date_month]={">=$(=MonthStart(vLastStartDate))<=$(=MonthEnd(vLastEndDate))"}>} [dim_ppl_employee_sk]))
)
/2
)

Thanks for any advice.

[rubenmarin]

Hi, set analysis is calculated before the table to reduce the data use, so it's not calulated row by row for each month.

To access the upper row data you can use Above(), like:

([YourExpressionForAMonth]
+Above([YourExpressionForAMonth]))/2

[TauseefKhan]

Hi @BoXiangWang,

Create two variables, vCurrentMonthStart and vPreviousMonthStart, to dynamically calculate the start of the current and previous months.

Let vCurrentMonthStart = MonthStart(Today());
Let vPreviousMonthStart = AddMonths(vCurrentMonthStart, -1);

// Measure for the current month's turnover
Sum({<Date={">=$(vCurrentMonthStart)<=$(MonthEnd(Today()))"}>} Turnover)

// Measure for average headcount of current and last month
(Sum({<Date={">=$(vCurrentMonthStart)<=$(MonthEnd(Today()))"}>} EndHeadcount) + Above(Sum({<Date={">=$(vPreviousMonthStart)<$(vCurrentMonthStart)"}>} EndHeadcount))) / 2

// Final measure for turnover rate
Sum({<Date={">=$(vCurrentMonthStart)<=$(MonthEnd(Today()))"}>} Turnover) /
((Sum({<Date={">=$(vCurrentMonthStart)<=$(MonthEnd(Today()))"}>} EndHeadcount) + Above(Sum({<Date={">=$(vPreviousMonthStart)<$(vCurrentMonthStart)"}>} EndHeadcount))) / 2)


***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****