You probably knew this was coming, but please post a sample packaged workbook so we can better understand what you're situation actually is (Superstore dataset would be good for this one). In the meantime this calc will allow you to identify leap years:
MAX(DATEPART('dayofyear', [Order Date]))
But it will be dependent on how your viz is setup. Also let us know what version of Tableau you are using, as LOD expressions will probably play into the ultimate answer.
To me, it sounds like you want to know how many days in the PRECEDING year. Try this out.
You may need to add MAX() or MIN() around the Date field depending on how you have your viz set up.
Notice in the table below, as soon as you cross the leap day, the preceding year shows 366 days.
However, you probably wonder why leap day doesn't show 366 days. Let's bring in the DATEADD('year',-1,[Date]) portion next to the real date to see what value Tableau is calculating as the One Year Ago date:
All the values show the exact day but one year ago, except of course leap day. Let's add a catch for leap years (you could do this a bunch of ways, here's one):
I think this helps answer the concept, but you will need to apply the concept to your level of detail of table functions. As shawnwallwork suggested, please post your workbook and we will be happy to help further.
Yo MP! These are some interesting replies. Just curious if you're coming back to let us know what is/isn't working for you. We helpers are Catnip junkies! So to speak.
Attached is a simplified workbook (tableau 9.0) that demonstrates the issue.
Toggle the 'Date Grouping' between months and days to change the dates presented in the table.
When "months" is selected, the table shows the rolling 12-month total up & including the given month (date entries displayed as end of month date eg. 30 June, 31 July etc). When "days" is selected, the table shows the rolling 365-day total up & including the given day (date entries displayed as day dates eg. 19 June, 20 June etc)
The desired behaviour is that the value seen for the dates when in "month mode" should equal those of equivalent date in the "day mode" i.e. the end-of-month date in each view of the date should be the same. And this is how it works except for leap-years when the rolling 12-month total ("month mode") will include data for 366 days, while the "day mode" totals only have 365 days of data.
As an example, the table entry for 31 August 2014 in "month mode" equals the table entry for 31 August 2014 in "day mode". However, the table entry for 31 December 2012 in "month mode" does NOT equal the table entry for 31 December 2012 in "day mode".
Need to have Tableau dynamically change the range of rolling calculation to have it include 366 records in the day-level calculation when in a leap year.
LeapYear.twbx 64.8 KB