I have tried to create a calculated filed that will show the invoiced dollars for the previous years and curent year based on today’s date. So today is 03/16/2018, then it would show for this year, 03/16/2017, 03/16/2016, and 03/16/2015 and everything from Jan to the date.
Everything works for all the years except curent year. Current year and date only pulls from January and no other month for 2018. Can’t figure out why.
case
when Invchead.FiscalYear = datepart(year,dateadd(year, -3, Constants.Today))and invchead.invoicedate<=dateadd(year, -3,Constants.Today) then invcdtl.ExtPrice
when Invchead.FiscalYear = datepart(year,dateadd(year, -2, Constants.Today))and invchead.invoicedate<=dateadd(year, -2,Constants.Today) then invcdtl.ExtPrice
when Invchead.FiscalYear = datepart(year,dateadd(year, -1, Constants.Today))and invchead.invoicedate<=dateadd(year, -1,Constants.Today) then invcdtl.ExtPrice
when Invchead.FiscalYear = datepart(year,Constants.Today)and invchead.invoicedate<=dateadd(year, 0,Constants.Today) then invcdtl.ExtPrice
else ‘0’
end