Previous Year YTD AND QTD based on Current date

[379SSS]

Hi all,

I need your assistance with the calculation of Previous Year YTD and QTD. Based on the current date, the same date should be used for both the previous YTD and QTD calculations. Below is my logic, but I'm unable to crack it.

previous year YTD:
Sum({<
[Fiscal Year] = {"$(vPrevFY)"}, // eg..2023  
[Date]={"<= $(=Date(Today() - 1))"}  // date should be based on max date of current year if max date of current year is 03/25/2024 then I want to calculate previous year YTD till   03/25/2023 instead of full year
>} Sales)

previous year QTD:
Sum({<
[Fiscal Year] = {"$(vPrevFY)"}, // eg..2023
[Fiscal Quarter Num] = {"$(vPrevYearsameFQtrNum)"}, //eg...2023 Q2 however this will in number format
[Date]={"<= $(=Date(Today() - 1))"}  // eg,, date should be based on max date of current year if max date of current year is 03/25/2024 then I want to calculate previous year QTD  till   03/25/2023 instead of full Quarter
>} Sales)
 

Your assistance would be greatly appreciated....

[brunobertels]

Hello 

try replace $(=Date(Today() - 1)) // will give 03/28/2024

by 

$(=addyears(date(today()),-1)) // will give bu derivating year value 03/28/2023 

[379SSS]

hi @brunobertels  I tried the above logic but this is giving all the historical data from 2021,2022,2023, it is not working

[ElisaF · Accepted solution]

Hello,

May be try following expressions:

Previous Year YTD:

Sum({$<
Date = {">=$(=Date(AddYears(YearStart(Today()),-1),'DD/MM/YYYY'))<=$(=Date(AddYears(Today(),-1),'DD/MM/YYYY'))"},CalendarField>}
Sales)

where CalendarField the list of all the fields from Calendar table that can be selected by end-user; e.i. Date,[Fiscal Year],[Fiscal Quarter Num]

Previous Year QTD:

Sum({$<
Date = {">=$(=Date(AddYears(QuarterStart(Today()),-1),'DD/MM/YYYY'))<=$(=Date(AddYears(Today(),-1),'DD/MM/YYYY'))"},CalendarField>}
Sales)

I assume the Date field is with date format  DD/MM/YYYY.

Also, I recommend using the numeric date field to avoid formatting issues.

The numeric field can be created in script as:  Num(Date) as Date_Num. In this case, remove the date formatting from the expressions, as for example:

Sum({$< Date_Num = {">=$(=AddYears(YearStart(Today()),-1))<=$(=Date(AddYears(Today(),-1),'DD/MM/YYYY'))"},CalendarField>}
Sales)

[379SSS]

@ElisaF Thanks for your help here! It's working with small adjustments.