Previous year revenues - Dynamic calculated field for a BAQ

Epicor ERP 10
1

Hello, I would like to set up a sales reporting BAQ for our Account Managers. I need to display sales revenues by customers…

Here are my needs (columns)

  1. PYTD Sales (Prior year to date sales - 2019)
  2. LYTD Sales (Last year to date sales - 2020)
  3. YTD Sales (Year to date sales - 2021)
  4. LYM Sales (Last year monthly sales)
  5. TYM Sales (This year monthly sales)

How can I set up a calculated field based on a dynamic date…

Thanks for your help!

2

@clcardinal Here are some fields I set up to run a report for company labor hours for comparable periods (we use company Fiscal Months and Years instead of going strictly by the calendar, so feel free to tweak accordingly).

Last Year YTD Labor (returns 2020 Fiscal Months 1-9 if run today)

sum(case when datediff(day , FiscalPer.StartDate,dateadd(month, -13, Constants.Today)) >= 0 and dateadd(month, -13, Constants.Today) >= FiscalYr.StartDate and dateadd(month, -13, Constants.Today) <= FiscalYr.EndDate
            
      then LaborDtl.LaborHrs
            
      else 0
            
      end)

Last Year Last Fiscal Period (returns 2020 FM 9)

sum(case when dateadd(month, -13, Constants.Today) >= FiscalPer.StartDate and dateadd(month, -13, Constants.Today) <= FiscalPer.EndDate

then LaborDtl.LaborHrs

else 0 

end)

Current Year YTD (returns 2021 FM 1-9)

sum(case when datediff(day, FiscalPer.StartDate,dateadd(month, -1, Constants.Today)) >= 0 and dateadd(month, -1, Constants.Today) >= FiscalYr.StartDate and dateadd(month, -1, Constants.Today) <= FiscalYr.EndDate
            
      then LaborDtl.LaborHrs
            
      else 0
            
      end)

Current Year Fiscal Period (returns 2021 FM 9)

sum(case when dateadd(month, -1, Constants.Today) >= FiscalPer.StartDate and dateadd(month, -1, Constants.Today) <= FiscalPer.EndDate

then LaborDtl.LaborHrs

else 0 

end)

We have this report set to run near the middle of each month so there’s no conflicts with the endpoints of the fiscal periods. Hope this helps.

3

Well I tried but it’s not working. When I run it, I only get “0” as results on all the lines…