Sum previous year up to the month where there is data for current year

[Chloe19]

I have the below formulas for previous year and current year.

I am trying to sum the previous year data but only up to the the month the current year has data for.

Previous Year:

sum({$ <TRAN_DATE={">=2022-10-01<=2023-09-31"}>} VOLUME)

Current:

if(sum({$ <TRAN_DATE={">=2023-10-01<=2024-09-31"}>} VOLUME)=0,'',sum({$ <TRAN_DATE={">=2023-10-01<=2024-09-31"}>} VOLUME))

 So for the image below, I would want the sum to be only up util may, but I don't want to hard code the month in.

[Vegar]

Maybe something like below?

sum({$ <TRAN_DATE={">='2022-10-01'<=$(=rangemin('2023-09-31', addyears(max(TRAN_DATE),-1))"}>} VOLUME)

[Chloe19]

Hi Vegar 

Getting an error, not sure where the issue is.

Error in set modifier ad hoc element list: ',' or ')'

[Vegar]

Looks like I didn't get all my parentheses right. (I am typing on my cellphone)

Try:

sum({$ <TRAN_DATE={">='2022-10-01'<=$(=rangemin('2023-09-31', addyears(max(TRAN_DATE),-1)))"}>} VOLUME)

The idea I had was with my solution was to limit the upper value to the lowest valye of 2023-09-31 and the date one year before your latest value.

[Chloe19]

Thanks for the help, can imagine the difficulty on the phone.

I did figure the bracket was missing but wasn't sure, was getting 0, still am. 

I checked individual if the rangemin was working,addyears,max etc but in the set analysis, I get 0.