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, 2022 in Tableau by Vaani
• 7,070 points
1,382 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

Unlock the Power of Data Visualization with Our Tableau Course.

answered Apr 12, 2022 by Neha
• 9,020 points

Related Questions In Tableau

0 votes
1 answer

combine two data sets in tableau so that I can show a forecast for the next 3 months

I believe the best approach is to ...READ MORE

answered Apr 5, 2022 in Tableau by Vaani
• 7,070 points
916 views
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 ffdfd
• 5,550 points
1,232 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,240 points
7,684 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 ffdfd
• 5,550 points
688 views
0 votes
1 answer

Error while connecting tableau to snowflake using username and password

"Couldn't resolve host name," says the error ...READ MORE

answered Mar 3, 2022 in Tableau by Vaani
• 7,070 points
829 views
0 votes
1 answer

How do I add a custom login and logout page in my Tableau Server on-prem?

I don't believe it is "officially" possible ...READ MORE

answered Mar 4, 2022 in Tableau by Neha
• 9,020 points
754 views
0 votes
0 answers

Tableau layout for multiple charts on one worksheet

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

Mar 10, 2022 in Tableau by Neha
• 9,020 points
437 views
0 votes
0 answers

Tableau report sharing

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

Mar 22, 2022 in Tableau by Vaani
• 7,070 points
415 views
0 votes
1 answer
0 votes
1 answer

How to share a workbook with client in Tableau who do not have Tableau account or tableau reader

It's possible that there isn't a way ...READ MORE

answered Apr 5, 2022 in Tableau by Neha
• 9,020 points
677 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP