Help with Prior Year

[MVenables]

Hi all,

We currently use a prior year Dax that is shown below. We do not use a normal Calendar as we use a financial Calendar for our business, so when we want to see Prior year sales for example we calculate this by using [AFBYearWeek]. 

The issue we have is the DAX below calculates backwards 100 rows so when we drill down to product level, it is over calculating then crashing, sometimes taking over an hour to load a table with data in.

Any suggestions on a better measure would be greatful as this was given to us by an external company and has caused us issues ever since.

SUMX (

    SUMMARIZE (

        'Calendar',

        'Calendar'[AFBYearWeek],

        "PYWS",

            CALCULATE (

                IFERROR ( ( SUM ( Sales_StoreProductDate[SalesValue] ) ), 0 ),

                FILTER (

                    ALL ( 'Calendar' ),

                    'calendar'[AFBYearWeek]

                        = MAX ( 'Calendar'[AFBYearWeek] ) - 100

                )

            )

    ),

    [PYWS]

)

[bhanu_gautam · Accepted solution]

@MVenables , It looks like the DAX measure you are using is causing performance issues due to the way it calculates the prior year sales by iterating over 100 rows.

Instead of calculating the prior year week dynamically in your measure, you can create a calculated column in your 'Calendar' table that stores the prior year week. This will make your measure more efficient.

PriorYearWeek = 'Calendar'[AFBYearWeek] - 100

Then you can use this calculated column in your measure

PriorYearSales =
CALCULATE(
SUM(Sales_StoreProductDate[SalesValue]),
FILTER(
ALL('Calendar'),
'Calendar'[AFBYearWeek] = MAX('Calendar'[PriorYearWeek])
)
)

[MVenables]

Hi Bhanu, Thank you for this suggestion, we have done some initial testing so far against what we had before and it does seem much better. The external company who created the original formula did something similar to what you suggested on Friday, but they are still using SUMX, so like you say i believe that is the main issue with this. Appreciate the help on this as i believe with more testing we will be using this within the business at some point.