I'm curious if it's feasible to build a single 'calculated field' that can be used as a Tableau dimension that has the following:
Recent Month (Dec 2022) Prior Month (Nov 2022) Recent Month - Year Ago (Dec 2021) YTD (Jan 1-Dec 31, 2021) Prior YTD (Jan 1-Dec 31, 2022
I tried to utilise an IF THEN statement, but the order of operations won't 'bag' November 2021 data into the two categories that are required. Do you have any suggestions? Is it even feasible to do so?
This is my take on it (based on data from superstores):
`If DATETRUNC('month',[Order Date]) =
DATEADD('month',-2,DATETRUNC('month',TODAY()))
THEN 'Prior Month'
elseif DATETRUNC('month',[Order Date]) =
DATEADD('month',-1,DATETRUNC('month',TODAY())) then 'Most Recent Month'
elseif DATETRUNC('month',[Order Date]) =
DATEADD('month',-13,DATETRUNC('month',TODAY())) THEN 'Most Recent Month a Year ago'
ELSEIF [Order Date (copy)] <= DATEADD('year', -1, [Order Date (MAX)])
AND [Order Date (copy)] >= DATE("1/1/"+ STR(YEAR([Order Date (MAX)])-1))
THEN 'PYTD'
elseif [Order Date (copy)] <= [Order Date (MAX)] AND DATETRUNC("year", [Order Date (copy)]) = DATETRUNC("year", [Order Date (MAX)])
THEN 'YTD'
END