Applying Alternate States with Variables in Formulas

[jalanhart]

I am creating some date pickers with alternate states to be able to choose a main period and comparison period which shows in a table below. that seems to be working so far, but my question is how I can default that to show a certain range when no values are selected. 

Previously, I had it set up where it would default to the latest completed month and then you could use filters like Year and Month to get to what you wanted. So I have variables created that say something like this : 

if( (GetSelectedCount([INVOICE_DATE.autoCalendar.Date])>0) ,
monthstart(min([INVOICE_DATE.autoCalendar.Date])),
monthstart(today(),-1))

and then then the measure is something like this 

num(Sum( { <
[INVOICE_DATE.autoCalendar.Date] = , [INVOICE_DATE.autoCalendar.Date] = {"<=$(=($(vMaxDateRange))) >=$(=$(vMinDateRange))"} >} NET_AMOUNT), '$#,##0')

My problem: How do I get the measure to default to the latest whole month from the variables AND then change when I use the date picker? I tried this but when nothing is selected in the date picker, it's shows the sum of ALL data which I don't like.

num(Sum( { <
[INVOICE_DATE.autoCalendar.Date] = , [INVOICE_DATE.autoCalendar.Date] = {"<=$(=($(vMaxDateRange))) >=$(=$(vMinDateRange))"} >} {[Current Period]} NET_AMOUNT), '$#,##0')

Thanks!

[rubenmarin]

Hi, the title of the post is about altern states, but none of the expressions is using an altern state, maybe you need to use the altern state of the dates selectors as: GetSelectedCount([INVOICE_DATE.autoCalendar.Date], True(), StateName) and min({StateName}[INVOICE_DATE.autoCalendar.Date])

[jalanhart]

Hi Ruben, 

The {[Current Period]} is my alternate state. I was trying to incorporate that in the measure which is the last formula I posted. 

I tried to put the formula you posted above in the date field of my date picker and got an error

GetSelectedCount([INVOICE_DATE.autoCalendar.Date], True(), [Current Period]) and min({[Current Period]}[INVOICE_DATE.autoCalendar.Date])

The alternate state is working as expected, I just need to make all the measures in the pivot table below it default to the latest completed month if the date picker has not been used yet, which is what my variables vMinDateRange and vMaxDateRange do

[rubenmarin]

Hi, for the 3rd parameter of GetSelectedCount it expects a literal with the name, try with: GetSelectedCount([INVOICE_DATE.autoCalendar.Date], True(), 'Current Period')

If(GetSelectedCount([INVOICE_DATE.autoCalendar.Date], True(), 'Current Period')>0) ,
monthstart(min({[Current Period]}[INVOICE_DATE.autoCalendar.Date])),
monthstart(today(),-1))

[jalanhart]

Sorry I am a little confused -- am I putting this in the measure is my tables or in the date picker or varibales? 

For example, I had a measure for Sales that used variables 

num(Sum( { < [INVOICE_DATE.autoCalendar.Year] =,
/INVOICE_DATE.autoCalendar.YearMonth]=, [INVOICE_DATE.autoCalendar.Month] =,
[INVOICE_DATE.autoCalendar.Date] = , [INVOICE_DATE.autoCalendar.Date] = {"<=$(=($(vMaxDateRange))) >=$(=$(vMinDateRange))"} >} NET_AMOUNT), '$#,##0')

This gives me a default latest whole month based on the definitions of those variables.

Is there a way that I can write a formula that says "if a {[Current Period]} is selected, show sales by that date range, if not selected, show sales for latest whole month (as shown above).

Thanks!

[rubenmarin]

Hi, I think it should be in the variables that define vMaxDateRange and vMinDateRange, so this ones filters the data based on the selections (or no selections) of the date picker with the altern state.

[jalanhart]

So I have the variable for vMinDateRange as an example that defaults to last who month of a date is not selected

if( (GetSelectedCount([INVOICE_DATE.autoCalendar.Date])>0) ,
monthstart(min([INVOICE_DATE.autoCalendar.Date])),
monthstart(today(),-1))

when I tried to switch the GSC to my variable for current period it no longer worked

if( (GetSelectedCount([Current Period])>0) ,
monthstart(min([INVOICE_DATE.autoCalendar.Date])),
monthstart(today(),-1))

 also tried with the {[Current Period]} syntax and that did not work either

[rubenmarin]

Hi, I did a check before the answer and it worked so something must be different but it will be hard to check witouh a sample app to check.

You can create one with some dummy data and the configuration that doesn't works, and upload it here so I can check what it needs.