Optimizing Dax measure that add

[AMNM2023]

Hello, I am looking for help with some DAX measures that I created.  They do work, I just need help with optimizing them so they can run a bit better.

The data set that I am using is comprised of two different sets of data, budgets and actuals.  Each month during a year, a data is entered into a budget (Period 1 Budget gets updated in October Period 2 Budget in November, etc).  These budgets only include information for the number period that it is for, and the remainder of the year, so Period 2 Budget would contain November through September (The fiscal year for the company is October through September).

The measure I made was to check to see if there is any data for the selected budget during a certain period, and if not, insert the companies 'actuals' in its place.  The idea being that as we go through the year the graph is slowly getting updated with actuals, but we don't want to lose track of past budgets.

The table that the measures would go into has a few columns that serve as labels on the left side, and then a column per period.  It would look approximately like this for periods 5, 6, and 7. You can see below how past periods have actuals that stay the same, while the future numbers in the rest of the forecast change.

I was able to create this measure, the example below is the measure that would generate the values for the period 1 column.

To explain some of the column names:

• 'Budget List'[Budget Order] refers to a list of the different budgets that exist.  I read not to filter on strings so I have numbers instead.  19 is Actuals, and 2 is a budget that does not get the actuals added to it.
• BP Active is a binary field that indicates whether a period is in the future or past (1 future, 0 past)
•  

Raw Cost Period 01 = 
VAR Earliest_Forecast_Date =
    CALCULATE (
        MIN ( 'Date Table'[End of Month] ),
        REMOVEFILTERS ()
    )
VAR Current_Month_Date =
    CALCULATE (
        MAX ( 'Date Table'[End of Month] ),
        'Fact Table'[PeriodNbr Integer] = 1
    )
VAR Actuals =
    CALCULATE (
        SUM ( 'Fact Table'[RawCost] ),
        'Budget List'[Budget Order] = 19,
        KEEPFILTERS('Fact Table'[PeriodNbr Integer] = 1),
        REMOVEFILTERS ( 'Budget List'[Forecast Label] )
    )
VAR Reduced_Actuals =
    CALCULATE (
        SUM ( 'Fact Table'[RawCost] ),
        'Budget List'[Budget Order] = 19,
        'Fact Table'[PeriodNbr Integer] = 1,
        KEEPFILTERS ( 'Date Table'[Date] > Earliest_Forecast_Date )
    )
VAR Forecast =
    CALCULATE (
        SUM ( 'Fact Table'[RawCost] ),
        'Fact Table'[PeriodNbr Integer] = 1,
         ( 'Date Table'[Date] > Earliest_Forecast_Date )
    )
VAR BPActive =
    CALCULATE (
        MIN ( 'Fact Table'[BPActive] ),
        'Fact Table'[PeriodNbr Integer] = 1
    )
VAR Result =
    CALCULATE (
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( 'Budget List'[Budget Order] ) = 2, Forecast,
            SELECTEDVALUE ( 'Budget List'[Budget Order] ) = 1,
                Forecast + Actuals,
            SELECTEDVALUE ( 'Budget List'[Budget Order] ) <> 2
                && SELECTEDVALUE ( 'Budget List'[Budget Order] ) <> 1
                && BPActive = 1, Forecast,
            SELECTEDVALUE ( 'Budget List'[Budget Order] ) <> 2
                && SELECTEDVALUE ( 'Budget List'[Budget Order] ) <> 1
                && BPActive = 0
                && [Earliest_Forecast_Date] < Current_Month_Date, Forecast,
            Reduced_Actuals
        )
    )
RETURN
    Result

The data table that I am using (Fact Table) looks like this:

And here is what the date table looks like.

They are joined from Financial Period Month End Date to Month End Date.

The measures that I wrote do work, but they are really slow.  Can anyone help with optimizing this a bit?

[DarkArchonNL]

1. Pre-Calculated Columns: Some variables in the measure are calculated based on static conditions and they can be moved to the table as calculated columns. For instance, BPActive can be pre-calculated at the row level in the 'Fact Table'.

2. Use of ALL function instead of REMOVEFILTERS: In some instances, you can use the ALL function instead of REMOVEFILTERS, which tends to be more efficient. You can replace CALCULATE (MIN ( 'Date Table'[End of Month] ), REMOVEFILTERS ()) with MINX(ALL('Date Table'), 'Date Table'[End of Month]).

3. Filtering Optimization: Instead of using the KEEPFILTERS function, try filtering within CALCULATE directly. For example, replace CALCULATE (SUM ( 'Fact Table'[RawCost] ), 'Budget List'[Budget Order] = 19, KEEPFILTERS('Fact Table'[PeriodNbr Integer] = 1), REMOVEFILTERS ( 'Budget List'[Forecast Label] )) with CALCULATE (SUM ( 'Fact Table'[RawCost] ), 'Budget List'[Budget Order] = 19, 'Fact Table'[PeriodNbr Integer] = 1, ALL( 'Budget List'[Forecast Label] )).

4. Simplify the Switch Statement: The switch statement has repeated conditions which can be grouped together to avoid redundancy, if it doesn't alter the logic of your calculations.

Remember, when optimizing, testing the performance after each change is crucial. The DAX Studio tool can help you in evaluating the performance of the DAX expressions, I highly recommend it!