Forum Discussion

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

Efficient Prior Year DAX Calculation needed please

Hi All,

 

Within the business our Financial Year starts 1st May each year so for example this current week is Year Week 202401 (the year & the week number). To calculate anything for prior year, we use the below DAX and do this for all our calculations. I believe it is not helping us and is pushing our capacity limits which we always seem to be hitting when loading in a lot of visuals with this on a page. Does anyone know a better way to calculate the below please? We have tried a few before that don't work with our Dynamic Calendar, using sameperiodlastyear doesn't work for example.

 

So the table is Calendar, it uses AFBYearWeek column which for example is 202401 this week our first week of the new financial year. It is calculating the DAX measure Count Of Products in the below example, and -100 weeks to get the figure from the year before. But this being used to go back 100 rows in a column each time its loaded in can't be doing it much good. Any help is appreciated. Thank You, Michael

 

Count Of Products Prior Year = sumx(SUMMARIZE('Calendar','Calendar'[AFBYearWeek],"PYWS",CALCULATE( IFERROR( ([Count Of Products]),0),FILTER(ALL('Calendar'),'calendar'[AFBYearWeek] = max('Calendar'[AFBYearWeek])-100))),[PYWS])
"DAX help"

1 Reply

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

     

     

    Count Of Products Prior Year = 
VAR _fiscal_year =       YEAR(EDATE( _today_date, 5))
VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 05, 01)
VAR _fiscal_year_end =   DATE ( _fiscal_year, 04, 30)

RETURN
CALCULATE(
    [Count Of Products],
    DATESBETWEEN('Calendar'[Date], _fiscal_year_start, _fiscal_year_end)
)