I had this request from a client to be able to see (let’s say sales) Sales by half-yearly. what made this one trickier, was that the company’s FY starts in July, so the first half of the Fiscal Year 2023 is from July 2022 – December 2022. Which translates to quarters 3 and 4 of a regular calendar year 2022. So the first thing we need to do is make Quarters 3 and 4, 1 and 2, to do this we just need to move each month back by 6 and then return the Quarter of that new date.

Step 1: Formula to return FY Quarters: 

Now we have new FY quarters, we can build the formula on this logic.

Step 2:  Create the new Start of Half-Yearly dates:

Months in FY Qtr 1 and 3 can be truncated to the start of the quarter, however, months in FY Qtr 2 and 3 have to move back 3 places. Using this formula,

 

The Result so far:

Step 3: Change the Fiscal year:

Remember we said 1 July 2022, is FY 23, So let’s go back and edit our formula sightly, to add 1 year to the date where the FY Quarters are equal to 1 and 2.

We now have the correct FY, for the halves.

Step 4: Create the Current Year and Previous Year formulas:

From here is simple to get the Current half year, using,

Since, I’ve moved the year by 1, from 2022 to 2023 in Step 3, we will have to go back 18 months to get the previous.

Step 5: Remove the unwanted columns:

The final result, after I remove the FY Qtr, Year, Quarter and Month of Order Date from the view, would look like this once your bring the Current Year and Previous Year formulas into the view.

I hope that gives you an idea of how to approach this problem, you might have to tweak it a bit to suit your client’s needs. I know I had to, for the purpose of this blog, once you understand the logic the rest should be easy.

Thanks for reading and Good luck!

Additional Resouces:

Fiscal Dates in Tableau

Using Date Functions with Fiscal Years

Jude Shu
Author: Jude Shu