Measure of previous 11 months headcount and attrition

[Anonymous]

Hi,

I have 2 tables of data, one for Headcount and the other for Attrition. What I need is to get a column chart visual that has the current month total attrition + 11 previous month attrition.

Here are the data that I have:

Attrition = SUM (previous 11 months) + current month / Total Current Headcount

i.e. Jan 2021 Attrition = Jan 2021 attrition + Sum (Feb 2020:Dec 2020)/ Jan 2021 HC = 11%

Month	2020 Vol. Terms	2021 Vol. Terms	2020 Headcount	2021 Headcount	2021 Attrition %
Jan	75	56	5126	5305	11%
Feb	45	37	5302	5378	11%
Mar	60	88	5419	5527	11%
Apr	45	110	5553	5603	12%
May	46	98	5605	5657	13%
Jun	38	112	5711	5754	14%
Jul	37	111	5813	5861	15%
Aug	46	99	5869	5919	16%
Sep	38	93	5931	5974	16%
Oct	61	116	5945	6019	17%
Nov	67	90	5969	6041	17%
Dec	41	99	6051	6098	18%

And below table that has the rolling 11 months total that could help with producing the column chart as showing below:

Jan 2021 Rolling 11 Month Total is = Sum (Terms Feb 2020: Dec 2020) = 524

Month	2021 Vol Terms	Rolling 11 Month Total	Attrition Rate
Jan	56	524	11%
Feb	37	535	11%
Mar	88	512	11%
Apr	110	555	12%
May	98	619	13%
Jun	112	679	14%
Jul	111	754	15%
Aug	99	819	16%
Sep	93	880	16%
Oct	116	912	17%
Nov	90	961	17%
Dec	99	1010	18%

My quesiton is how to get the same viz in Power BI? 

Note: the tables in Power BI are like below (Headcount Table & Attrition Table):

Headcount Table
Month	2020	2021
Jan	75	56
Feb	45	37
Mar	60	88
Apr	45	110
May	46	98
Jun	38	112
Jul	37	111
Aug	46	99
Sep	38	93
Oct	61	116
Nov	67	90
Dec	41	99

Attrition Table
Month	2020	2021
Jan	5126	5305
Feb	5302	5378
Mar	5419	5527
Apr	5553	5603
May	5605	5657
Jun	5711	5754
Jul	5813	5861
Aug	5869	5919
Sep	5931	5974
Oct	5945	6019
Nov	5969	6041
Dec	6051	6098

[amitchandak · Accepted solution]

@Anonymous , Assume you have attrition table and headcount table both join to a date table and you are using data table in axis or in visual

Assume you have measures [Attrition] and [Headcount]

MTD Attrition= CALCULATE([Attrition],DATESMTD('Date'[Date]))

MTD headcount= CALCULATE([Headcount],DATESMTD('Date'[Date]))

or

CALCULATE(lastnonblankvalues('Date'[Date], [Headcount]) ,DATESMTD('Date'[Date]))

last 11 month till last month

Rolling 11 = CALCULATE([Attrition],DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,-11,MONTH))