Combine Multiple Measures Into Single Measure

CatManKuhn — Tue, 25 Jul 2023 15:32:17 GMT

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!

topic Combine Multiple Measures Into Single Measure in DAX Commands and Tips

Combine Multiple Measures Into Single Measure