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

0 votes

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.

image

Apr 11 in Tableau by Vaani
• 7,020 points
61 views

1 answer to this question.

0 votes

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

answered Apr 12 by Neha
• 8,560 points

Related Questions In Tableau

0 votes
1 answer
0 votes
1 answer

Issues with the Range value in Tableau

One of the best solution is to ...READ MORE

answered May 9, 2018 in Tableau by sdfg
• 5,550 points
715 views
0 votes
1 answer

How to combine rows with same information in Tableau

@Ghost, Tableau has a function RANK_UNIQUE which you ...READ MORE

answered May 25, 2018 in Tableau by Atul
• 10,230 points
6,059 views
0 votes
1 answer

Error: Comaparing date value with YEAR in Tableau

You have to ensure that the comparisons ...READ MORE

answered Jul 11, 2018 in Tableau by sdfg
• 5,550 points
237 views
0 votes
1 answer
0 votes
1 answer
0 votes
0 answers

Tableau layout for multiple charts on one worksheet

On one spreadsheet, I have 15 pie ...READ MORE

Mar 10 in Tableau by Neha
• 8,560 points
31 views
0 votes
0 answers

Tableau report sharing

We have a number of historical tableau ...READ MORE

Mar 22 in Tableau by Vaani
• 7,020 points
19 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP