Use Date range in SET Analysis, yet ignore a Date selection

[mikelutomski]

Hello,

I need some help with some SET Analysis.

I'm creating a KPI to sum a value for the most recent 3 months that cannot change, and then I want another KPI that shows the sum of the same value for a 3 month date range a user selects.

1st KPI

Sales = $3.5M for Feb, Mar, Apr

2nd KPI

Sales = $1.1M for Jan, Feb, Mar (3 months based on date a user selects).

When the user selects Mar, I want the 1st KPI to stay the same.

My Date field is formatted as M/D/YYYY

Here is my code:

Sum({$<[Date] ={'>=$(=AddMonths(Max([Date]),-3))<=$(=Max([Date]))'}, [Entity Category] = {'Sales'}>} [Amount])

I've tried entering the "[Date] = ", but this then cancels out my initial date range above.

I've tried entering the "[Date] = e([Date])", but this only excludes the date that is selected.

Any help would be appreciated.

[aarkay29]

May be this

1st KPI

Sum({1<[Date] ={'>=$(=Date(AddMonths(MonthStart(Max([Date])),-3),M/D/YYYY))<=$(=Date(Max([Date]),M/D/YYYY))'}, [Entity Category] = {'Sales'}>} [Amount])

2nd KPI

Sum({$<[Date] ={'>=$(=Date(AddMonths(MonthStart(Max([Date])),-3),'M/D/YYYY'))<=$(=Date(Max([Date]),M/D/YYYY))'}, [Entity Category] = {'Sales'}>} [Amount])

[mikelutomski]

I had tried that too with no luck.  Thanks Aar!

[sunny_talwar · Accepted solution]

May be this

Sum({$<[Date] ={'>=$(=AddMonths(Max({<[Date]>}[Date]),-3))<=$(=Max({<[Date]>}[Date]))'}, [Entity Category] = {'Sales'}>} [Amount])

[mikelutomski]

That did it! 

I've never seen that before.  At first I thought wanted me to hard code the date in there.

Thank you Sunny!

[sunny_talwar]

I am just ignoring selection in Date field within your inner aggregation... this might look more familiar

Sum({$<[Date] ={'>=$(=AddMonths(Max({<[Date] = >}[Date]),-3))<=$(=Max({<[Date] = >}[Date]))'}, [Entity Category] = {'Sales'}>} [Amount])

[mikelutomski]

So you're doing SET Analysis inside SET Analysis. 

I think I tried that before with something else and couldn't get it to work, and then just forgot about trying it again.

Thanks for your help!

[sunny_talwar]

So you're doing SET Analysis inside SET Analysis.

Yup!!