Hello All,
Looking for some guidance on how to do a Current YTD vs Previous YTD in terms of sales (extprice). For example I want 3 columns that show:
PYTD $ amount….example: Sales $ through 5.30.22
CYTD $ amount….example: Sales $ through 5.30.23
Different between PYTD $ vs CYTD…$ amount
What have you tried so far?
It should be a pretty straightforward BAQ with aggregate functions ( sum() ). There are date filters in the table filters selection. You would make one subquery for current year to date, and another for previous, then join them together and display them.
For the current year to date, I would simply do date = this year and it would pull all updated records for this year so far but for the previous year - how would I do that without manually inputting a date? Would something like today()-1 for year?
Depends…
Where is the data going to be consumed? Dashboard? PowerBI? Excel?
You could create two calculated fields based on the sales date.
Month = Month(SalesDate)
Year = Year(SalesDate)
Aggregate on those fields. is (sum $$$ group by month, year)
Then you don’t need to set the date fields and could quickly see more than the two years.
Lots of options here.
As to your date question. You could filter the whole dataset to only bring back data from the last 2 years. Somthing like SalesDate >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
There are all kinds of options for dates in the BAQ,. Look through those and see if you can find something that works.
I always forget there are tools to help…
For this - it’ll give me sales for 5/30/2022 but how can I do it so it is 1/1/2022 - 5/30/2022?
Add multiple rows in your filters. There is less than, greater than etc.
In addition to all of the good advice that you have already received, there’s another option that wouldn’t require quite as much subquery or join complexity:
Add a table criteria on the InvcHead table for InvcHead.FiscalYear >= specified expression and use the expression Constants.Year - 1
That will give you every invoice since the first day of the prior fiscal year (Jan. 1, 2022, assuming you ran the report today and your company uses calendar years as fiscal year).
Then it is a matter of joining invoice detail and summing for prior versus current. The calculated field for doing so would be something like:
CY Sales: sum(case when InvcHead.FiscalYear = Constants.Year then InvcDtl.ExtPrice else 0 end)
Full PY Sales: sum(case when InvcHead.FiscalYear = Constants.Year-1 then InvcDtl.ExtPrice else 0 end)
PYTD Sales: sum(case when InvcHead.FiscalYear = Constants.Year-1 and InvcHead.InvoiceDate <= dateadd(year,-1,Constants.Today) then InvcDtl.ExtPrice else 0 end)
Sorry for necroposting, but does anyone know why Epicor decided not to make FirstDayOfYear and FirstDayOfPrevYear BAQ Constants like they did for days, weeks, and months? Sure would make things like this easier.
