How to calculate moving 12 month average base on two differnt tables

[Mavericks334]

Hi,

I have two table, one that gives me the total attrition and the other the headcount?

The attrition % is calculated by divinding the the total attrition/headcount

I need to calcualte a moving 12 month average. I tried David Churchward moving averages post, however i am unable to get the result.

Probably because i have two different tables and the calculations is based on these tables.

Could some one help me out.

Tables are HC: {Dept Id, Month, Headcount} Attrition:{Dept Id, Month, Headcount}, Date Table {Date, Month-end, Next month Startdate}

 

[Andrew Fergus]

Hello

Can you post waht you have tried so far?

I'm thinking that if you want to know the value as at today, then you can use a sumif formula to add the attrition values that occured in the past 12 months. Repeat for headcount and divide attrition by headcount, e.g. something like this : Sumif(Attrition!MonthRange, ">=" & Now()-365) / Sumif(HC!MonthRange, ">=" & Now()-365)
That isn't the actual formula, jut a guideline.

If however you want the value between two date values of which one is not today, then that would be a different formula.

Andrew

P.S. Whoops - I just saw this was a question in the powerpivot forum. My solution has nothing to do with powerpivot whatsoever, just a simple Excel solution.

 

[Mavericks334]

=CALCULATE([Value_Corrected1],datesbetween(Dates[Date],[Selected_Moving_Month_End_Date],Year_Period[Selected_Month_End_Date]),Gives me the hc for the last 13 month, then i divide it by 13 to get the average. headcount during that period

Selected_Moving_Month_End_Date = =lastdate(dateadd(Year_Period[Previous_Month_End_Date],-1,YEAR))
Selected_Month_End_Date = lastdate(Year_Period[Month_End_Date]) to get my data.