Calculating headcount variance year on year when data is already a total

[#1]

Hello,

I would like to calculate a variance for headcount (as an example) year over year. In my example data set the headcount in May 2015 is 104, the headcount in May 2016 is 116 therefore the variance should be 12.

I want to show two figures:

(1). The latest headcount of all the months selected e.g. if the year 2016 is selected, and months Jan, Feb, Mar, Apr & May are selected I would like the answer for headcount to be the headcount of May 2016 which in my example is 116.

(2). The variance of the headcount year on year, if year 2016 is selected, and months Jan, Feb, Mar, Apr & May are selected, so in this example May 2016 is 116, May 2015 is 104 so the answer should be 12.

I have achieved (1) by the following expression using 'MonthYear' from my master calendar

sum({ < MonthYear={'$(=max ( MonthYear ))'} > }Headcount)

But cannot work out an expression to calculate (2).

I have attached an example document.

Any help appreciated.

Thanks

[sunny_talwar]

May be try this for May 2015

Sum({< MonthYear={"$(=Date(MonthStart(AddYears(Max(MonthYear), -1)), 'MMM-YYYY'))"}>}Headcount)

and for variance:

Sum({<MonthYear = {'$(=Max(MonthYear))'}>} Headcount) - Sum({< MonthYear={"$(=Date(MonthStart(AddYears(Max(MonthYear), -1)), 'MMM-YYYY'))"}>}Headcount)