Show Today's Sales and Same Day Last Year's Sales

[asmithids]

Hello,

I am creating a straight table to show the current day's sales amount and the same date last year's sales amount with variance by store (see attached screen shot of table layout and data model).   I have found postings on comparing sales amounts between the current date and the same day last year, but the suggested solutions are not applicable to what I am trying to solve.  I am having difficulty putting the current date and the current date sales amount with the same day last year's sales amount on the same row by Store. 

I need to just show the current day's sales (i.e. Max(date) function) and the same date last year's sales .  For example, sales for all stores for today 1/9/2016 and 1/9/2015 with variance.  Each day the dashboard needs to only show that day's sales with prior date sales. 

I believe the right approach is to use set analysis expressions, but I'm not sure of the specific syntax to get the table to show the data. 

Any help on the coding is always highly appreciated! 

Thank you,

[sunny_talwar]

I think if I understand your requirement correctly, you will need to use Above() function to achieve what you are looking to do.

[settu_periasamy]

Hi,

Normally we do like this

Current year max date sales

=Sum({$< Date={"$(=Date(Max(Date)))"}>}Sales)

Previous :

=Sum({$< Date={"$(=Date(AddYears(Max(Date),-1)))"}>}Sales)

Not sure is this your need..

[asmithids]

Hi,

I tried the expressions, but no data is appearing. I did change the Sales field to the sales field in the data model.  The updated expressions are now: 

• =Sum({$< Date={"$(=Date(Max(Date)))"}>} [Net Sales]) 
• =Sum({$< Date={"$(=Date(AddYears(Max(Date),-1)))"}>} [Net Sales])

Not sure why no data is appearing. 

[sunny_talwar]

I think since you have the date field in your dimension, the simple set analysis won't work for you. You will need to use Above() function to make it work. For the exact expression, you will need to provide a sample data.

[asmithids]

Hi Sunny,

Please see attached sample qvw and data source.

Thank you,

[sunny_talwar]

Try this:

=If(Sum({$< Date={"$(=Date(Max(Date)))"}>} [Net Sales]) > 0, Above(Sum({<Date>}[Net Sales])))

Sorry I just realized this previous day and you are looking for previous year. But the idea is still to use Above() function to achieve that

[asmithids]

Hi Sunny,

I added your expressions and I'm still not getting any data. I attached the actual qvw that I am using.  I do not have any filters selected at all.  Strange.... 

Also, I compared the straight table data to the source data and the prior period numbers do not agree with the source data spreadsheet. 

Thank you,

[sunny_talwar]

My laptop is out of battery so won't be able to help you before 10 hours. Hopefully someone else might be able to expand on my logic, if not I can help once I return home

Best,

Sunny

[asmithids]

Ok.  Thank you!