Looking for YOY YTD formula that works with fiscal years in tableau

Question

In Tableau, I'm searching for a YOY YTD calculation that works with fiscal years.

Method 1 can be checked here. However, it is not applicable to fiscal years. The filter for the current year begins in January. Is it possible to make approach 1 work with fiscal years? Note: I tried using default properties->start fiscal year in July, but it didn't work.

See "goal in tableau screenshot below" for further information.

Answer 1

If you computed what you're showing in Tableau rather than mocking it up in Excel, you're almost there.

Create a computed field called "FY-Number" if you don't already have one. January -> 1, February -> 2, and so on. Perhaps you already have such a field because you're sorting in that order.

All you need now is a filter to choose FY-Number 7 assuming your "average" is a "total" (using AVERAGE). This is something you COULD accomplish manually. You might do the following if you want this to be automatic and always total down to the most recent month. There are probably better methods to do it, but this one works.

For constructing the grid, I have a hierarchy of FY-numbers and Fiscal-Month-numbers.

Calculate a running number for the month that we can maximise, a sequential number that crosses all years. FMRun was calculated using a formula.

[FY num]*12 + [FM num]

For December 2021, the maximum is 270. Tableau is capable of locating the maximum. Then, to find the maximum month number, reverse that.

Formula for FMInvert:

{FIXED :(MAX([FMRun])/12 - FLOOR(max([FMRun])/12))*12}

For the data provided, this has a value of 6.

Finally, apply this filter, which I've dubbed whatmax, to that, which we want to be true:

if  [FM num]   <= [FMinvert] then TRUE
else FALSE
END

And you're done.

For reference, here is the workbook: https://public.tableau.com/app/profile/wade.schuette/viz/YOY-YTD-answer/Dashboard1?publish=yes

Unlock the Power of Data Visualization with Our Tableau Course.