YoY reduction in headcount based on annual percentage value

[Anonymous]

Hi, I am trying to calculate year over year reduction in headcount as per a given percentage value of previous year’s headcount.

The logic is as follows:

Initial Headcount = 10

Percentage reduction annually = 20%

So,

Headcount in 1st Year = Initial Headcount – (20% of Initial Headcount)
                                        = 10 – (20% of 10)
                                        = 10 – 2 = 8

Headcount in 2nd Year = Headcount of 1st Year – (20% of Headcount of 1st Year)

                                        = 8 – (20% of 8 )

                                        = 8 – 1.6 = 6.4

Headcount in 3rd Year = Headcount of 2nd Year – (20% of Headcount of 2nd Year)

                                        = 6.4 – (20% of 6.4)

                                        = 6.4 – 1.28 = 5.12

Please help in doing the same!

Thanks in advance!

[amitchandak]

@Anonymous , If rate is 20 then convert to % like 20/100 , if it already % , leave it as is

Measure  =

var _percent = selectedvalues(Param[percent]) //assumed what if

var _year = selectedvalues(Param[percent])  // assumed what if or take hard coded value 1 or 2 or 3

return

[Headcount]*power(1-[_percent],_year )

refer power : https://youtu.be/wZOzDIVSs84

[Anonymous]

This approach using what if parameters worked for my initial calculations .... thanks! 
I am now figuring out a way such that I can view the YoY decrease in headcount for every year. In other words, headcount for year 1, then year 2 and so on .... I would not want to see the cummulative decrease at any single year as my final output, rather decrease year by year ... Pls share your ideas on the same! Thanks again! 

[amitchandak]

@Anonymous , Do you have a date or year?

Refer this example with date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

//Only year vs Year, not a level below // This can work with the year table

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

[Anonymous]

Hi Amit, 
Yes, I have an year table. 
Also, the annual reduction percentage is a user input in my case. As a result, the values I am expecting as output are of headcounts being reduced YoY and not the diff%
Ideally, if a single measure can carry out this calculation for atleast 3 years, would be really helpful.