Set analysis addition

[Anonymous]

I am trying to use set analysis to add two different balance amounts based off of a filter and a selection.

The addition is correctly working, but the first set analysis function is not returning the right data values.

How the first set analysis should work is: If the Cape Toggle = Only Cape, then we want to take the Current Balance on 4-29 -2016.

The second set analysis is taking the current balance based off the current selection. It looks at the current selection and then goes back to the previous year of the current selection and takes the current balance of that date (so if 10-31-16 is selected, it will look at the greatest date in 2015 which would be 12-31-2016)

=sum({<[Cape Toggle] = {'Only Cape'},[Production Date]={"=2016-04-29"}>}[Current Balance])

+

sum({$<[Cape Toggle] = {'No Cape'},[Production Date]={"$(=date(max({1<Year={$(=max(Year)-1)}>} [Production Date])))"}>}[Current Balance])

[#2]

Hi Ashley,

for first part of expression

=sum({<[Cape Toggle] = {'Only Cape'},[Production Date]={"=2016-04-29"}>}[Current Balance])

you are using yyyy-dd-mm format for date, is that the format shown for on selection filters?

[Anonymous]

No it is not, should i change it to the way the the selection filter displays?

[Anil_Babu_Samineni]

How is your date format

And try to remove = from first set operator

[Anonymous]

  where?

[Anil_Babu_Samineni]

How your date field format looks

And try this

sum({<[Cape Toggle] = {'Only Cape'},[Production Date]= '2016-04-29' >}[Current Balance])

+

sum({$<[Cape Toggle] = {'No Cape'},[Production Date]={"$(=date(max({1<Year={$(=max(Year)-1)}>} [Production Date])))"}>}[Current Balance])

[Anonymous]

It errors on that expression on the red part:

sum({<[Cape Toggle] = {'Only Cape'},[Production

Date]= '2016-04-29' >}[Current Balance])

[sunny_talwar · Accepted solution]

Have you just tried this:

=sum({<[Cape Toggle] = {'Only Cape'},[Production Date] = {"$(=Date(MakeDate(2016, 4, 29)))"}>} [Current Balance])

+

sum({$<[Cape Toggle] = {'No Cape'},[Production Date]={"$(=date(max({1<Year={$(=max(Year)-1)}>} [Production Date])))"}>}[Current Balance])

[Anil_Babu_Samineni]

Under mOderated try this

[Anonymous]

Thank you this worked perfectly!!!!