Fiscal Year to Date vs. Prior Fiscal Year to Date in Tableau
Вставка
- Опубліковано 23 лип 2024
- Connect with us:
•Upcoming Tableau Classes: www.eventbrite.com/o/onenumbe...
•Book an Office Hour with Eric: www.eventbrite.com/e/tableau-...
•Subscribe to the OneNumber Newsletter: onenumber.biz/newsletter
•Follow Eric on LinkedIn: / eric-parker-37513249
Video Description
Many organizations report against a fiscal calendar which doesn't align with the standard calendar year. If your organization's fiscal year starts in October or July, you know what I'm talking about.
We've created a handful of resources documenting how to use calculations to monitor year to date performance compared to prior year to date performance, but we've never addressed how to do this if you're operating on fiscal years.
*Disclaimer: this approach will work best if your organization's fiscal years starts on the first day of a month. If it varies, this approach might not be right for you.*
Inspired by my friends at the Federal Aviation Administration in a recent training, I created this video to demonstrate how to use calculations in Tableau to compare fiscal year to date values to prior fiscal year to date values.
Tableau Workbook Download
public.tableau.com/app/profil...
Related Resources
Full documentation of calculations on the blog post here: onenumber.biz/blog-1/2022/10/...
Year to Date versus Prior Year to Date: onenumber.biz/blog-1/2021/4/7...
Mastering Tableau Date Calculations: onenumber.biz/blog-1/2021/4/1...
How to Create a Custom Calendar for Tableau (in Excel): onenumber.biz/blog-1/2021/6/6...
How to Calculate Date Difference in Business Days in Tableau: onenumber.biz/blog-1/2019/3/2...
Chapters:
0:00 Background
3:40 Creating a Fiscal Date Field
4:50 Hardcoded Fiscal Year to Date Calc
5:52 Current Fiscal Year Calc
8:09 Dynamic Fiscal Year to Date Calc
8:44 Prior Fiscal Year Calc
9:12 Dynamic Prior Fiscal Year to Date Calc
11:52 Calculation Validation
Need help writing your own fiscal year calculations? Sign up for an office hour with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909
Ready to master Tableau? Check out our lineup of upcoming classes! www.eventbrite.com/o/onenumber-15678294163
This was a huge discovery for me. I had created YTD comparison calculations for several CY based dashboards but for hours this morning, I had been fighting Tableau with getting it shifted to properly show the FY comparisons and could not figure it out without parameters or massive number of complex calculations. You first calculation to create a FY date adjustment calc for calcs only was the missing piece and I can't believe I hadn't thought of it before. Thank you! Once I created that and replaced all the [DATE] fields with your calc, everything lined up by FY and the calculation comparing the Current vs Prior finally showed the correct number.
We're so glad to hear you found this helpful. Thank you for letting us know!
Thank You Eric. This was a fantastic demonstration of how to calculate fiscal periods in Tableau; I especially liked that you first demonstrated the VISUAL setting for Tableau fiscal period but then how this does not apply to date calculations. I have always initially struggled with dates in every system I have ever used, including SQL. Your video addressed how to work with custom fiscal periods. Thanks to you, I can now create user determined fiscal periods for a current project where I have to show fiscal year OR project year spend.
I'm really enjoying your videos Eric. Your style is natural, relaxed, and you are so competent. Thank you for your content.
Thank you so much for the kind words Paul! Funny enough, I just finished shooting a video for Fiscal Quarter to Date vs. Prior Fiscal Quarter to Date so stay tuned for that in the coming weeks!
This was a huge help, thank you for this!
You are welcome!
love this
Thanks so much!
This is so helpful, I really appreciate it. I was driving myself crazy trying to run some reports. I'm new to table perhaps do the same for other date part? Quarters?
Thanks Sandra! How about this? ua-cam.com/video/KsoUty8ZqR0/v-deo.html
Eric, this is a great video. Thank you! Do you have a suggestion for calculating the maximum order date OR today's date if it is more recent? E.g. I have a dataset with infrequent updates. Since this is based on the maximum date, this can lead to discrepancies if in the previous year orders were recorded on a day/month that came later than the maximum date for activity in the current fiscal year. Essentially, I want the calculation to pick the most recent of the two values for the current fiscal year to compare to the previous fiscal year.
Thank you! Yes. In short, something like this should do it. {MAX([Date])} Here's a video on that topic: ua-cam.com/video/1MjfxmmPNBk/v-deo.html&ab_channel=OneNumber-TableauExperts
Thank you for this! How do I change the names of my fiscal years - FY 2022 (for example) to AY21-22 (this is how we call our years because they are academic years)?
I think you could try right-clicking and aliasing them? Unfortunately I think you'd have to do that one year at a time. You could also try creating a custom calculation and doing something like DATEADD('month',6,[Date Field).
More on those ideas in this Academic Year/Tableau webinar here: ua-cam.com/video/gJEqZEKGr84/v-deo.html&ab_channel=OneNumber-TableauExperts
This is helpful! There's really not much tutorial out there when it comes to how to create FYTD calculation in Tableau. I don't fully understand the part around 11:01'', and when I try to apply this to my case, the tableau throws out an error message of 'can not mix the nonaggregate function with aggregate function...) I am dying... why seemingly easy tasks elsewhere (ie. SQL, excel) is so difficult in Tableau...
We're glad to hear that you found this helpful! You are getting the aggregation error because you are trying to reference an aggregated calculation in your IF statement.
For instance, Tableau doesn't like this:
IF YEAR([FY Date]) = 2022 THEN SUM([Sales]) END
Tableau prefers this:
SUM(IF YEAR([FY Date]) = 2022 THEN [Sales] END)
More on that topic here: onenumber.biz/blog-1/2018/1/30/tableau-cannot-mix-aggregate-and-non-aggregate-comparisons-or-results-in-if-expressions
You may need to apply this FY logic to an earlier calculation to get this to work the way you want.
Thank you very much for the tutorial, could you please add a parameter where I can select "Current period" and "Current Period vs. Prev Period"?
Hi Ines. How do you define period? Like a 13-period year?
Hi again. Current period for me is Current Fiscal Year (FY23 = April 2022- March 2023). And I have already realised I wanted to say “&” not vs. Many thanks for your attention.
@@inesmorillo3121 So I don't have the logic handy for your exact situation but I wonder if you're looking for something like this where you can custom code your own logic? ua-cam.com/video/PM0O3uVJ-Bw/v-deo.html
This is so helpful, I'm trying to do the same for Quarters "Current Financial year Quarters Vs Previous Financial year same Quarters" could you please help me out!.
Hi Sri, good to hear, thank you! We haven't done Fiscal Quarters to Date but here's a video on QTD vs. Prior QTD. Mixing that with the Fiscal Year concepts here should get you the output you need! ua-cam.com/video/KsoUty8ZqR0/v-deo.html
Just adding a new reply because we now have a video for Fiscal QTD versus prior Fiscal QTD! onenumber.biz/blog-1/2023/3/22/fiscal-quarter-to-date-vs-prior-fiscal-quarter-to-date-in-tableau
Somehow my prior fiscal date is giving date for the whole month instead of being the same number of days as current fiscal. any idea?
I would double-check the DATEPART('dayofyear',...) portion of the calculation. Sounds like maybe something there is off!
@@onenumbertableau please help on it. I really need help on this.
Hi Eric,
I have done the same thing for Indian financial year which Start from 1st April to 31st March. but the issue is if i am selecting 04-04-2024 then in the last year same month it is giving till 04-04-2023 but when i am selecting 27-03-2024 then it is taking last year till 28-03-2024.
And if you will check for datepart ('dayofyear', order date fy (for calcs) ) then you will find there is missing 31st day and also it is considering 59th day for 4 date in May month.
Could you please check and guide me how i can resolve this ?
You my friend have run into the challenge of Leap Years in Year to Date comparisons. Here's a blog post we did on the topic: onenumber.biz/blog-1/2020/2/20/accounting-for-leap-years-in-tableau-ytd-calculations
I guess an alternate way to do this to account for that would be:
Prior FYTD Sales:
IF (YEAR([Order Date FY]) = YEAR([Current FY]) - 1))
AND
MONTH([Order Date FY]) < MONTH([Current FY])
OR
(MONTH([Order Date FY]) = MONTH([Current FY]) AND DAY([Order Date FY)
Hi Eric could you please make a vedio on it so that will be better understanding for everyone, because I'm having this issue from last 2 week but still the issue isn't resolved.
@@sumitpandey2415 Hey Sumit, I've added it to our list but it'll probably be a while before it gets made and published. In the meantime, I'm sure there are answers out there! I can help via an office hour (www.eventbrite.com/e/tableau-expert-office-hours-tickets-42691995909) or if that's not possible, you can ask your question in the Tableau Forums or Reddit and some folks there may be able to help. Good luck!
Hi, can you please help with current fiscal year MTD vs prior fiscal year MTD calculation logic please
Hi, I'm not sure what exactly you're looking for. Do your fiscal months not start on the first day of the calendar month?
In my case fiscal year starts from April to March. So, suppose for fiscal year 2024- i have data from April -23 to Mar-24 right now, CYMTD should be Mar 24 Sales and prior year MTD should be Mar-23. Calculation should be dynamic.
@@arijitbanerjee7400 I think you're looking for something like this: ua-cam.com/video/bQ2b9FDb7c0/v-deo.html&ab_channel=OneNumber-TableauExperts
This is normal mtd and prior mtd. I want Fiscal year MTD vs prior Fiscal Year MTD.
Just like you posted FISCAL YTD VS PRIOR YEAR FISCAL YTD; i want FISCAL MTD VS PRIOR YEAR FISCAL MTD
@@arijitbanerjee7400 Maybe combining the last video I sent with the Max date option displayed here will help, otherwise I recommend booking an office hour and we can work on your problem together. ua-cam.com/video/1MjfxmmPNBk/v-deo.html&ab_channel=OneNumber-TableauExperts
what if your data has future dates populated, but you only need for the present FY, how do you fix it. Because the Max in the current FYwill pick the future FY
In that case, I would create a Fiscal Year of Today calc and reference that instead for your max date. Something like this.
YEAR(DATEADD('month',3,TODAY()))
@@onenumbertableau Thank you so much! This has resolved the error I was experiencing with my report.
@@Esther_Worships Great to hear, you are welcome!