Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
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
CurrentMonthForecastAnytime 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!
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
| User | Count |
|---|---|
| 15 | |
| 8 | |
| 4 | |
| 4 | |
| 3 |
| User | Count |
|---|---|
| 24 | |
| 12 | |
| 12 | |
| 10 | |
| 5 |