Amend expression to show cumulative

[Chloe19]

I have the below expression which returns the volumes for only the latest month 

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'YYYY-MM-DD') & '<=' &Date(MonthEnd(Max(Date)), 'YYYY-MM-DD'))"} >}TTRs)

I would like to amended it to show me:

1. cumulative 

2. cumulative for the 2nd latest month (i.e. 2 months ago)

Any help will be appreciated.

Thanks

[Lech_Miszkiewicz]

1. Not sure what you mean by cumulative - that can very much depend on dimensions used in the chart. If dimension is not linked with Date then below already is cumulated total for 2 months (current and previous)
2. as for 2 months expression note -1 as a parameter in MonthStart() function... Date(MonthStart(Max(Date),-1)

   1. Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date),-1), 'YYYY-MM-DD') & '<=' &Date(MonthEnd(Max(Date)), 'YYYY-MM-DD'))"} >}TTRs)

[Chloe19]

I was able to work out previous month using the below add months

Sum({<Date = {"$(='>=' & Date(Addmonths(MonthStart(Max(Date)),-1), 'YYYY-MM-DD') & '<=' &Date(Addmonths(MonthEnd(Max(Date)),-1), 'YYYY-MM-DD'))"} >}TTRs)

As to your question, take a look at the image of the table.

I have volumes and YTD cumulative (rolled up each day).

1. I am trying to achieve the same with the last 2 columns by month

2. I just realised I would need the 2 months ago values to be lined up with the latest month date, though I am sure that's got something to do with the way I structured the model (any advice apprciated)

Meaning for May, so for 2024-05-30 i would want the volume for the previous month but the same day i.e. 2024-04-30 (red line in image) 

That way I can compare the results by day when in a line chart.

[Lech_Miszkiewicz]

Hi @Chloe19 

you say:

"I just realised I would need the 2 months ago values to be lined up with the latest month date, though I am sure that's got something to do with the way I structured the model (any advice apprciated)"

Those kind of things I prefer to handle in data model as it is extremly hard to assign not associated data to some records. So to offset records like in your chart i would create reference calendar which would link multiple DateKeys with the label value you want to show in the chart. That way I can use field "DateUsedInTheChart) as my dimension of the table and then using flags in expression I can associate correct periods with it. 

To keep expression simple I normally would do this for all combinations of cumulative periods or offsets. That way your model works of the standard data association and there are no issues caused by sorting of the table or improper use of formulas.

cheers