How to use Set Analysis to sum until the last available record with data instead of the last calendar date

[Anonymous]

My expression looks currently like this:

Sum({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))>=$(=Date(AddMonths(MonthStart(Date(Today())), - 12)))"}>} [Amount])


This is how the data looks like:

Companies	MonthYear	Amount
Company1	201701	10000
Company1	201612	20000
Company2	201701	-
Company2	201612	-
Company2	201611	35000
Company2	201610	17000
Company3	201701	-
Company3	201612	50000
Company3	201611	10000
Company3	201610	24000
Company3	201609	40000
Company3	201608	-
Company3	201607	-
Company3	201606	32000
Company3	201605	18000

This is what I want to achieve:

Example: *Company1 has data until 201701 --> sum data from 201601 until 201701

              *Company2 has data until 201611 --> sum data from 201511 until 201611

              *Company3 has data until 201612 --> sum data from 201512 until 201612

But because I am using Today() as my limit I am having the following displayed: (THIS IS NOT WHAT I WANT TO ACHIEVE)

Example: *Company1 has data until 201701 --> sum data from 201601 until 201701

              *Company2 has data until 201611 --> sum data from 201601 until 201701

              *Company3 has data until 201612 --> sum data from 201601 until 201701

How do I need to change the expression so that my dashboard will sum the [Amount] field from the last available month with data for every company until the 12 months before that date?

PS: Today() = 23 Jan 2016

[sunny_talwar]

Try this I guess

Sum(Aggr(If(MonthYear >= AddMonths(Max(TOTAL <Company> MonthYear), -12) and MonthYear <= Max(TOTAL <Company> MonthYear), Amount), MonthYear, Company))

[Anonymous]

Nice try, but it doesn't really work.

Any other ideas?

[sunny_talwar]

Would you be able to share a sample where this isn't working?

[Anonymous]

I have attached an application with your expression, by looking inside of the application you will be able to understand where my difficulty lies with creating the desired chart.

[sunny_talwar]

I am not sure what the issue is, I seems to get the same number for Amount you are looking to get?

[Anonymous · Accepted solution]

I figured out what expression I have to use:

I just had to change Today() with the maximum month where amount was available

Sum({$< [Calendar Date] = {"<=$(=Date(MonthEnd(Date(Date(MonthEnd(Date(Max(Aggr(Max({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))"}, [Amount] = {"<>0"}>} [Calendar Date]), [Companies])))))))))>=$(=Date(AddMonths(MonthStart(Date(Date(MonthEnd(Date(Max(Aggr(Max({$<[Calendar Date] = {"<=$(=Date(MonthEnd(Date(Today()))))"}, [Amount] = {"<>0"}>} [Calendar Date]), [Companies]))))))), -12)))"}>} [Amount])

Thank you stalwar1 for your help!!!