Date Logic

[has75042]

Hey Everyone,

I need urgent help automate dates.  In the report, we have two tabs one for  this TY Saturday and another one for LY Saturday. 

For example, the first tab will use coming Saturday Fiscal Year date 3/16/2019 and the second tab will use   LY Fiscal Saturday date 3/17/2018. How can automate the dates using the attached file.

The table I am using is in attached file. 

[Shubham_Deshmukh]

Hi,

If possible, create day(Saturday, Monday,...) in SQL script itself during extraction as it will save calculation time on front end, then use below expression with variables for TY and LY saturdays,

//vFiscalYearStart = YearStart(today(),0,4)

//vLYFiscalYearSTart = AddYears(YearStart(today(),0,4),-1)

//vLYToday = AddYears(today(),-1)

//vToday = date(today())

This year : 

=Sum({<dates ={">=$(=vFiscalYearStart)<=$(=vToday)"},day={'Saturday'}>}sale)

Last Year:

=Sum({<dates ={">=$(=vLYFiscalYearSTart)<=$(=vLYToday)"},day={'Saturday'}>}sale)

Regards,

Shubham