Efficient rolling total measure

[AlexaderMilland]

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 Power BI service for the users of the measure.

I've already limited the data amount and the amount of potential filters to reduce calculation resources, however it does not seem to be doing the job well enough.

"

Items: Running Total This Year =

var selectedisoweek = ALLSELECTED(OrderLines[ISOWEEK])

var selectedntype = ALLSELECTED(ProductTable[nType])

var selectedbrand = ALLSELECTED('ProductTable'[Brand])

var selectedtype = ALLSELECTED('ProductTable'[producttype])

var returnval =

CALCULATE (

    SUM ( OrderLines[items] ),

        FILTER(ALL('OrderLines'),'OrderLines'[ISOWEEK] <= MAX( 'OrderLines'[ISOWEEK] )

    && OrderLines[ISOWEEK] in selectedisoweek

    ),FILTER('ProductTable',ProductTable[nType] in selectedntype&&ProductTable[producttype] in selectedtype && ProductTable[Brand] in selectedbrand)

    ,'Calendar'[CalendarDate].[Year] = 2022

)

return returnval

"

Any suggestions for optimizing the query? 
Could this simply be resolved using a year-to-date calculation instead?

[m3tr01d]

hi @AlexaderMilland 

I think there are two things that impact the performance of your measures :
1) You are filtering a table in both Filter statement. Filtering a table is really expensive when done on a huge fact table

If you look at some of the videos from SQLBI, its the first thing that they will explain to you is avoid filtering table, always filter a column instead.

2) Second thing is you are not using your calendar table. ISOWEEK should be an attribute of your calendar and you should filter the column ISOWEEK from your calendar table and not the one from the Fact table. Iterating on a column of a dimension will be faster.

You can also remove any columns in the OrderLines table as they take space and impact comprehension of each columns.

[m3tr01d]

Hi @AlexaderMilland 

There are some things I don't understand :

var selectedisoweek = ALLSELECTED(OrderLines[ISOWEEK])
var selectedntype = ALLSELECTED(ProductTable[nType])
var selectedbrand = ALLSELECTED('ProductTable'[Brand])
var selectedtype = ALLSELECTED('ProductTable'[producttype])

var returnval =
CALCULATE (
    SUM ( OrderLines[items] ),
        FILTER(ALL('OrderLines'),'OrderLines'[ISOWEEK] <= MAX( 'OrderLines'[ISOWEEK] )
    && OrderLines[ISOWEEK] in selectedisoweek
    ),FILTER('ProductTable',ProductTable[nType] in selectedntype&&ProductTable[producttype] in selectedtype && ProductTable[Brand] in selectedbrand)

    ,'Calendar'[CalendarDate].[Year] = 2022
)

What are you trying to accomplish by explicitly filtering the Product table with your variables?

[DataInsights]

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

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]

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