Sorry Scott., Its my bad I should have given a little bit explanation.
FY Order Date <= Latest Date AND // this compares given date to the max date in the dataset
FY Year = DATEPART("year", Latest Date ) AND // this compares the year of the given date to the year of the max date in the dataset
(int(((month(FY Order Date))%12)/3)+1) = DATEPART("quarter", Latest Date ) AND // this is for the quarter comparison
DATETRUNC( "month", FY Order Date) = DATETRUNC("month", Latest Date) // and finally the month
I used the quarter as well since I start my fy year in dec and want the first 3 months (dec-feb) to be the Q1, mar-may as Q2 and so on.
If you need further help, you can post your sample workbook
I ended up using a nested IF statement after looking at all of the options others have offered up here.
So my code looks a bit like this:
IF 2019 Fund THEN
IF Date = ‘2019 Date’ then
ELSEIF Date = ‘2019 Date’ THEN
ELSE IF 2018 Fund THEN
IF Date = ‘2018 Date’ then
ELSEIF Date = ‘2018 Date’ THEN
I know my solution is probably not the most eloquent but it gets the job done.
I guess what I find frustrating is the Tableau makes the assumption that everyone's length of time is just 12 months and that all years run from Jan - Dec. In my case a fund can run for 24 months and the fiscal year runs from Oct - Sep. Granted, I might be missing some of the understand in the more automated coding samples but the logic suggested seems to fail somewhere along the way and by using a more manual solution, it's consistent and repetitive.
Thanks all y'all for your help and suggestions!
Hey whatever works! Happy you found a solution