How do you refer to a year in set analysis

[aetingu12]

Dear all, 

I have 3 years of monthly data (31 January 2017 until 31 March 2019) and I would like to get the end of year amounts for the past three years.

1 Column for each year, so

2017 would be 31 December 2017

2018 would be 31 December 2018

2019 would be 31 March 2019

I have written the expression as such:

if(ValueList('2017','2018','2019') = '2017',
sum({1 <Date={"=$(=YearEnd(addmonths(max(Date),-24)))"} >}BAL),
if(ValueList('2017','2018','2019') = '2018',
sum({1 <Date={"=$(=YearEnd(addmonths(max(Date),-12)))"} >}BAL),
if(ValueList('2017','2018','2019') = '2019',
sum({1 <Date={"=$(=max(Date))"} >}BAL))))

the dimension is just the Valuelist.  I need to make sure that the Data selections won't effect the output.  So it will always look at the latest data and go 2 years back, 1 year back and the current period. 

Any help is very much appreciated.

[dplr-rn]

I see you have {1 in your set analysis. that should work for ignoring all filters.
if you want to be specific, Add something like below into your set analysis
Year=,Month= etc. (substitute appropriate filter names)

[aetingu12]

Thank you for coming back to me.  I have attached an example to show what I am talking about.  It seems like I am getting the same amount for each year.

[dplr-rn]

Issue is primarily = after the " 

sum({1 <Date={"$(=max(Date))"} >}BAL)

also i would create a year, month columns (ideally a master calendar).

See attached

[rubenmarin · Accepted solution]

Hi, can you try with?:
Pick(match(ValueList('2017','2018','2019'), '2017','2018','2019')
,sum({<DATE={"$(=YearEnd(addmonths(max(DATE),-24)))"} >}AMOUNT)
,sum({<DATE={"$(=YearEnd(addmonths(max(DATE),-12)))"} >}AMOUNT)
,sum({<DATE={"$(=date(max(DATE)))"} >}AMOUNT)
)

[aetingu12]

As a quick follow up. I want to make sure that a date selection in a list doesn't effect the calculations.  I tried putting 1 after sum({1<.... but a selection of the date is changing my table using your expression with pick.
Thanks,

[rubenmarin]

The {1} should be for the DATE used to create the filters:

Pick(match(ValueList('2017','2018','2019'), '2017','2018','2019')
,sum({<DATE={"$(=YearEnd(addmonths(max({1} DATE),-24)))"} >}AMOUNT)
,sum({<DATE={"$(=YearEnd(addmonths(max({1} DATE),-12)))"} >}AMOUNT)
,sum({<DATE={"$(=date(max({1} DATE)))"} >}AMOUNT)
)

[aetingu12]

Thank you very much!