Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hi all,
I am attempting to clean up some formulas due to poor performance. I am working on creating a headcount forecast using monthly headcount data, turnover data, and a backfill rate. I have 13 measures that determine the headcount forecast for 12 months in the future. I would like to combine these 12 into a single measure to see if this improves performance. Here is an image of 3 of these formulas and how I would like to display them in a single measure called TEST Forecast:
I would like TEST Forecast to display the values in the Headcount Forecast Turnover + Backfill Count Helper columns.
Here is the DAX for Headcount Forecast Turnover + Backfill Count Helper:
Headcount Forecast Turnover + Backfill Count Helper =
var HeadcountForecastPM = CALCULATE (
[Headcount Actual],
FILTER (ALL('Date'),
'Date'[Serial Month]
= MAX ( 'Date'[Serial Month] ) - 1
)
)
var TurnoverForecast = HeadcountForecastPM * [Monthly Turnover Rate 3Y Average]
var Backfill = TurnoverForecast * [Backfill Percent Value]
var CurrentMonthForecast = IF ( [CurrentMonthYear] = YEAR(TODAY())&MONTH(TODAY()), HeadcountForecastPM - TurnoverForecast + Backfill)
Return
CurrentMonthForecast
Here is the DAX for Headcount Forecast Turnover + Backfill Count Helper 2:
Headcount Forecast Turnover + Backfill Count Helper 2 =
var HeadcountForecastPM = CALCULATE (
[Headcount Forecast Turnover + Backfill Count Helper],
FILTER (ALL('Date'),
'Date'[Serial Month]
= MAX ( 'Date'[Serial Month] ) - 1
)
)
var TurnoverForecast = HeadcountForecastPM * [Monthly Turnover Rate 3Y Average]
var Backfill = TurnoverForecast * [Backfill Percent Value]
var HeadcountForecast = HeadcountForecastPM - TurnoverForecast + Backfill
RETURN
HeadcountForecast
Lastly, here is my formula for TEST Forecast:
TEST Forecast =
var HeadcountForecastPM = CALCULATE (
[Headcount Actual],
FILTER (ALL('Date'),
'Date'[Serial Month]
= MAX ( 'Date'[Serial Month] ) - 1
)
)
var TurnoverForecast = HeadcountForecastPM * [Monthly Turnover Rate 3Y Average]
var Backfill = TurnoverForecast * [Backfill Percent Value]
var CurrentMonthForecast = IF ( [CurrentMonthYear] = YEAR(TODAY())&MONTH(TODAY()), HeadcountForecastPM - TurnoverForecast + Backfill)
--------------------------------------
var TurnoverForecast2 = CurrentMonthForecast * [Monthly Turnover Rate 3Y Average]
var Backfill2 = TurnoverForecast2 * [Backfill Percent Value]
var HeadcountForecast = CurrentMonthForecast - TurnoverForecast2 + Backfill2
--------------------------------------
Return
CurrentMonthForecast
Anytime I sum the variables I will get a true sum, but I want them to be appended so that the column shows a value for each month rather than a sum for the current month.
Any help is greatly appreciated!
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
19 | |
14 | |
8 | |
7 |