Previous year to date

[#1]

Hi,

I have been asked to build a report that details the sales for each month for the current year and the sales amount for the same month of the previous this will build over the year and then start again in 2018 showing all of 2017. For example the report will be scheduled for the 1st Feb 2107 and this will show all of January 2017 and also January 2016.

Basically I am looking for previous year to date total. Show when the report run in March it will show me all of January and February 2016 sales in one column

My expression is
Sum({$<
[LinkId] = {"FIN"},
[Bill Code]-={"REBATE"},
[Bill Date]  = {"<=$(=MonthEnd(addyears(today(),-1)))>=$(yearstart(addyears(today(),-1)))"}
>}[Bill Value])
/

This unfortunately returns records prior to Jan 2016.

I have split the bill date code and put it on the report as an object and it returns 01/01/2016 and 31/01/2016.

This will be scheduled on the 1st of every month. The data refreshes every night so on the 1st of each month it will only hold the data for the last day of the month so I don't see why my expression wont work. Any help would be gratefully received

[sunny_talwar · Accepted solution]

May be this:

Sum({$<
[LinkId] = {"FIN"},
[Bill Code]-={"REBATE"},
[Bill Date]  = {">=$(=YearStart(Today(),-1))<$(=MonthStart(Today()))"}
>}[Bill Value])

If you have a specific Date Format, you might need to add Date() function

Sum({$<
[LinkId] = {"FIN"},
[Bill Code]-={"REBATE"},
[Bill Date]  = {">=$(=Date(YearStart(Today(),-1), 'DateFieldFormatHere'))<$(=Date(MonthStart(Today()), 'DateFieldFormatHere'))"}
>}[Bill Value])

[#3]

Thanks very much.

You pointed me in the right direction and I made a slight amendment and it now works. The expression is

Sum({$<
[LinkId] = {"FIN"},
[Bill Code]-={"REBATE"},
[Bill Date]  = {">=$(=YearStart(Today(),-1))<$(=MonthEnd(addyears(today(-1),-1)))"}
>}[Bill Value])

Only issue I may have is that the application updates at 1 am on the first of the month. Will the monthend then be Feb or Jan,? I guess Feb, how do I trim a day and a year off?

[sunny_talwar]

So if it is

Feb 1st 2017, you want to see Jan 1st 2016 till Jan 31th 2016, right?

Mar 1st 2017, you want to see Jan 1st 2016 till Feb 29th 2016?

May be this:

Sum({$<
[LinkId] = {"FIN"},
[Bill Code]-={"REBATE"},
[Bill Date]  = {">=$(=YearStart(Today(),-1))<$(=MonthStart(AddYears(Today(),-1)))"}
>}[Bill Value])

[#5]

I need to test this before month end is there anyway I change the system date from today to 1st Feb or can you show me how I can alter the query to kid it into thinking today is the 1st Feb

Thanks in advance

[sunny_talwar]

Today() +  7 should give you Feb 1st, right?

Try this:

Sum({$<

[LinkId] = {"FIN"},

[Bill Code]-={"REBATE"},

[Bill Date]  = {">=$(=YearStart(Today() +  7,-1))<$(=MonthStart(AddYears(Today() +  7,-1)))"}

>}[Bill Value])