Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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
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!
@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 ])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |