Simple aggregate but it won't work for me

[Ben1981]

I have a really simple date table and a measure that calculates a value for each month and I want to create a second measure that does a rolling aggregate of the first measure each month but I just can't get it to work, I'm getting an error of exceeded resources with my current DAX measure.

Month | Measure1 | Measure2
May     |   5   |  5
June     |   2   |  7
July      |   10  |  17
Aug     |   1    |  18

Sept    |  4      | 22

[Kedar_Pande]

@Ben1981 

Measure2 = 
CALCULATE(
SUM('Table'[Measure1]),
FILTER(
ALL('DateTable'[Month]),
'DateTable'[Month] <= MAX('DateTable'[Month])
)
)

This approach should improve performance, as it limits row-by-row calculation complexity, avoiding excessive resource usage errors.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[shafiz_p]

Hi @Ben1981  Don't know your data and model. But you can try this to find out rolling sum.

Measure2 = 
VAR CurrentMonth = MAX('Date'[Month])
RETURN
CALCULATE(
    [Measure1]
    FILTER(
        ALL('Date'),
        'Date'[Month] <= CurrentMonth
    )
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Ben1981]

Thats what I already have and my visual just says query has exceeded the available resources. [Update] If I remove some other fields from my visual it works but it the calculation stops once it gets into the forecasted months and just repeats the last actual over and over.

My data model:
Volume Table that has a month column that starts from Jan 2024 to Oct 24 (updates monthly with new month) and a customer count column.
Date Table: various date fields but contains dates from 2001 to 2030
These two tables have a relationship via month.

I then have a measure that basically does a sum of the customer volume with some filters but also forecasts it beyond Oct 24 up to Dec 24. 

I have a table visual with a column of months from the date table that is filtered from Jan 24 to Dec 24 and then the measure above.

I now need to create a second measure that aggregates that forecast measure over the months.

[shafiz_p · Accepted solution]

try this alternative version:

Rolling Measure = 
VAR CurrentMonth = MAX('Date'[Month])
VAR FilteredTable = 
    FILTER(
        ALL('Date'),
        'Date'[Month] <= CurrentMonth
    )
RETURN
SUMX(
    FilteredTable,
    [Measure1]
)

Hope this helps!!

[Ben1981]

Again it seems to work fine up to the last actual date in Oct but then forNov and Dec which is the forecasted values it just repeats the output from Oct. 

Update: Ignore me, it's working now. I don't what I did but it all seems fine, I think it might have been my forecast measure causing the issue. 

Thanks for the help 🙂