Forum Discussion

MVenables's avatar
MVenables
Icon for Advocate II rankAdvocate II
2 years ago
Solved

Help with Prior Year

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...
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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])
    )
    )

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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's avatar
    MVenables
    Icon for Advocate II rankAdvocate II
    2 years ago

    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.