Previous Date Variable in Set Analysis

[asmithids]

Hello,

I'm trying to show current and last year's weekend date sales (see attached).  I am using a variable below to calculate last year's weekend date.  The text box shows that the variable is working because I have a filter on the weekend field set to 5/27/2017 and the variable is showing the comparable date last year.  The issue is that the "All Fuel Types Prior Year" column in the straight table is showing the filter date (i.e. 5/27/2017) value and not the value from 5/28/2016. 

How anyone seen this issue before?  Thank you in advance for any assistance!

Variable:

vPYWeekending: =Max(FormattedWeekEndDate)-364

Expressions:

1. All Fuel Types Current Year:  Sum({<Year={$(=Max(Year))}>}FuelMtrDtl_Posted_Units)
2. All Fuel Types Prior Year:  Aggr(Sum({$<FormattedWeekEndDate={'=$(vPYWeekending)'}>}FuelMtrDtl_Posted_Units),FormattedWeekEndDate)

[ariel_klien]

Try to use this:

vPYWeekending: =addmonths(Max(FormattedWeekEndDate),-12)

BR

Ariel

[asmithids]

Hi Ariel,

Thank you for the reply.  That did not work.  It produced a previous date of 12/30/1899.  See attached.

[ariel_klien]

I think the format of the field is not recognize...

try

vPYWeekending =addmonths((Max(Date#(FormattedWeekEndDate,'MM/DD/YYYY')),-12)

[asmithids]

Same result (i.e. 12/30/1899). 

[ariel_klien]

can you attach data sample?

[asmithids]

Hi Ariel,

Sorry for the delay.  See attached sample app.

Thank you!

[ariel_klien]

Hi,

you need to erase the equal sine in the variable....

and you don't need the Date#....

[asmithids]

Hi Ariel,

Made the change but, the Prior Year column is still showing the Current Year.  See attached.

I get the right numbers when I hardcode the prior year date in the PY expression like this:

Sum({$<FormattedWeekEndDate={'5/28/2016'}>}FuelMtrDtl_Posted_Units)

Even using the following expression without Aggr still shows the current year numbers:

Sum({$<FormattedWeekEndDate={'=$(vPYWeekending)'}>}FuelMtrDtl_Posted_Units)

None of the expressions above are expanding the variable correctly.  The KPI in the screen shot does correctly expand the variable.   

I believe the straight table is in the same "grain" with respect the the dimensions being used. 

[ariel_klien]

Try

Sum({$<FormattedWeekEndDate={"$(vPYWeekending)"}>}FuelMtrDtl_Posted_Units)