I’m trying to compare sales for this year to the same period in all previous years. EX today is 08/07 so I want to look at sales for 01/01 - 08/06 for every year that I have data. I’ll likely run into the same problem when doing WoW, MoM, QoQ so a generic solution is ideal. Whats the best way to do this?
You should be able to this by extracting a date part and filtering for the max of the current year. Here is another option that is a little more dynamic and involves a parameter:
Day([Opportunity Created DAY]) >= Day([pStartDate]) and Month([Opportunity Created DAY]) >= Month([pStartDate]) and Day([Opportunity Created DAY]) <= Day([pEndDate]) and Month([Opportunity Created DAY]) <= Month([pEndDate]) and Year([pStartDate]) = Year([pEndDate])
Where pStart and pEnd with
DateTrunc("month", Today()) and
DateAdd("day", -1, Today()) would give the dynamic YTD
This is very interesting. I think you are saying that a parameter can have a formula as it’s value. So pStart parameter has the logic DateTrunc(“month”, Today()) and will dynamically compute each time it is used. How do I do this? I have searched the online documentation all over and cannot find how to put a formula as the logic inside a parameter.