Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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)
)
Thank you very much!