Sales Last Year Same Month Day

[madnanansari]

I am working Sales table have 3 years data with the below attributes:

[Date]

[Sales Amount]

The requirement is to show a TABLE where for each [Date], Sales for Last year same Month day should also come.

For example: Against the date 10-Dec-2017, the last year same Month day sales also come which is sales for 11-Dec-2016. (Sunday this month same as sunday last year same Month which is 11th december).

If I subtract 364 from [Date]; it will give me Last Year Same day.

[madnanansari]

I need sales for the day which is 364 days back. I don't need the sale for same month same day number.

If let say today is 27th Dec 2017 and wednesday then I need the sales for wednesday the 28 Dec 2016 which is exactly 364 days back. I cannot compare Wednesday (27 dec 2017) with tuesdays (27 dec 2016) ; that's not correct.

[antonaks]

Ok. Then you can create complex key with week number and day number.

But... I correctly understood that in the case of January 2016 and January 2017, you will compare 01/01/2017 and 03/01/2016, because this is one day of the week?

[antonaks]

I apologize, at first the date was written incorrectly. Already fixed.

[antonaks]

[madnanansari]

Any guidance?

[antonaks]

LOAD

     Date,

     Week(Date) & '-' & num(WeekDay(Date)) as WeekNum_WeekDay, // Key that you need

     Sales

FROM

[sunny_talwar]

Is the As-Of-Table approach not working? Have you tried it already?

[madnanansari]

[AsOf Master Calendar]:

Load

[Date],

Date([Date] - 364,'D/M/YYYY') as [Date LYSMSD],

-364 as [Date LYSMSD Diff]

Resident [Master Calendar];

I am using the standard master calendar.

Now when I try: sum({$<[Date]= {"$(=Max([Date LYSMSD]))"}>} [Sales]), its not bringing the results.

[sunny_talwar]

Try this

AsOfTable:

LOAD Date as AsOfDate,

     Date,

     'CY' as Flag

Resident [Master Calendar];

Concatenate (AsOfTable)

LOAD Date as AsOfDate,

     Date([Date] - 364,'D/M/YYYY') as Date,

     'PY' as Flag

Resident [Master Calendar];

Now you need to use AsOfDate in your chart instead of using Date as a dimension and this expression

Sum({<Flag = {'PY'}>}Sales)