Month to date and quarter to date sales for last year using set analysis

[300sss300]

Hi,

I am using the following set analysis in a text box for MTD and QTD Sales for the current year.

=num(sum({<Date={">=$(=QuarterStart(Max(Date)))<=$(=Max(Date))"}>}Sales),' #,##0')

=num(sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"}>}Sales),' #,##0')

Now I want to show last year's QTD and MTD sales upto the same date corresponding to current year. for  example if current year MTD and QTD sales is upto 19 July 2019, then last year's sale should also be upto 19 July 2018 (from full data for the year 2018)

MTD 2018 = 01 Jul 2018 to 19 July 2018

anyone please explain how to do it using set analysis.

Thanks

[sunny_talwar · Accepted solution]

Try with AddYears function to go one year back from each of the date in your set analysis

=Num(Sum({<Date = {">=$(=QuarterStart(AddYears(Max(Date), -1)))<=$(=AddYears(Max(Date), -1))"}>} Sales),' #,##0')

=Num(Sum({<Date = {">=$(=MonthStart(AddYears(Max(Date), -1)))<=$(=AddYears(Max(Date), -1))"}>} Sales),' #,##0')

[300sss300]

Thanks!

How can I combine this with Week starting Thursday or for example I want to use another set analysis using WeekStart or WeekEnd.

Precisely, I need "Thursday to be my first day of week starting Jan 2019 i.e. 03 Jan 2019 should be my ist day of week for Week 1 till date.

Same thing for last year i.e. 4 Jan 2018 should be Ist day of week 1 and it should continue till 02 Jan 2019 (Wednesday). I mean last week of 2018 should complete on 02 Jan 2019.

[sunny_talwar]

You should be able to make use of WeekStart function with it arguments to adjust the first day of the week.