Forum Discussion
Help with Prior Year
Anonymous , 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])
)
)
Super UserAnonymous , 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])
)
)
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.