Re: Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field
First, I think you can simplify the YTD conditions:
FDM >= YearStart(today(),-1) and FDM <= addmonths(MonthEnd(today()),-13)
I'd use it as a flag, it will help to build simpler front end expressions:
if(FDM >= YearStart(today(),-1) and FDM <= addmonths(MonthEnd(today()),-13), 1, 0) as PriorYTD
For QTD, similar:
if(FDM >= QuarterStart(today(),-1) and FDM <= addmonths(MonthEnd(today()),-4), 1, 0) as PriorQTD
Now, the bigger question is not technical, but business logic. If today is January, our expressions will return no data for Prior YTD because there is no date level data. What the user should really expect?
Same with the QTD if today is the first month of the Quarter.
MTD simply doesn't make sense in this situation.
You need to talk with the business users to find out what they expect. Maybe calculate pro-rated amount, e.g. if today is May 5th, it is a 5/31 part of the month, hence the Prior period to date should include 5/31 of the amount for the corresponding month(?) Just thinking loud...