Forum Discussion

AlexaderMilland's avatar
AlexaderMilland
Icon for Helper III rankHelper III
4 years ago

Efficient rolling total measure

I've created a functional rolling total measure to add to my matrix table. It is however very inefficient, takes extremely long to compute and often requires more than 1GB RAM limit you encounter on ...
Need Help
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

AlexaderMilland,

 

It's simpler to use a year-to-date calculation, and specify the year in a slicer/filter.

 

 Be sure to mark your Calendar table as a date table. Right-click the table and select "Mark as date table":

 

 

AlexaderMilland's avatar
AlexaderMilland
Icon for Helper III rankHelper III
4 years ago

Having trouble making the year-to-date last year measure.

I do not want it to sum to the same days,I want it to sum up the same weeks from the last year. 

ISOWEEK = 0 is excluded, e.g. the first few days of the year. 

This year week 1 starts on 3rd of January, so i want the YTD measure on week 3 to be the sum of week 1+2+3 for 2022. This works fine with a regular YTD for this year, e.g. it sums from 3/1/2022 to 23/1/2022. 

When i do the same for last year (2021) i want it to sum from 4/1/2021 to 24/1/2021, since week 1 start on a later day in 2021. 

 

 

 

Any ideas?

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    AlexaderMilland,

     

    Try this measure. Add the column ISOWEEK to the Calendar table and use this field as matrix rows. Use a slicer/filter to specify 2022 (and thus get 2021 data).

     

    Items: Running Total Last Year =
CALCULATE (
    SUM ( OrderLines[items] ),
    'Calendar'[Year] = MAX ( 'Calendar'[Year] ) - 1,
    'Calendar'[ISOWEEK] <= MAX ( 'Calendar'[ISOWEEK] )
)