SO lately, I have been trying to get my head around Date Calculations in Tableau! Yes, they can be straightforward and super but when you are asked to make bespoke Fiscal Years (FY) and to show cumulative months for this year compared to last year it got a little crazy.
I was not sure about writing a blog on this, as I am not too sure I can explain it correctly, and then I figured someone would need this hefty calculation as a lot of business have a lot of different FY. I was encouraged (pushed) by Andre de Vries to blog about it and helped by Andrew Pick to figure this one out… Here goes.
To Start with I had to make a Parameter to show case what values I wanted the users to select – here we have chosen FY2014, FY2015, FY2016, FY2017, Rolling 12m, and All. (ignore YTD for now) – I had this calcs already but the YTD cumulative months is what I was stuck with. Here below is a my parameter;
So here is the date calculation I already had from previous working outs to get to the bespoke FY
The calculation is valid inside dance *:-)*
It is great that Tableau colours the different measures you use in your calculations to make them easily identifiable – it helps when things break and you need to narrow down the problem. Thanks Tableau well done on that! Therefore, we know the purple means it is a Parameter, the orange is a Measure or Dimension and the blue is a calculation method. I am asking my ‘Period All’ to fit between the dates shown.
This worked perfectly, well until I was asked to make cumulative dates compared to last year and this year, I’ve made a chart below which I hope explains it better.
Here is where I needed help from The Information Lab and for this I had Andrew Pick to my assistance, we went back and forth on emails trying to understand what we actually needed and how to make it work in the predefined calculation I already had.
Andrew had sent me this calculation below to get the YTD cumulative value, and inside it are two other calcs so I have listed them below also.
First – YTD Calculation
Then – Month Conversion
Next – Today Month Conversion
So that was the three calculations I had to make my YTD cumulative work, I was really thankful for the help and off I went to go and add this calculation in to my main ‘Period All’ calc.
I went away and worked on it for an hour or two adding the YTD calculation as it appears as show or hide to the filters shelf and the parameter was working perfect except for when you would select ‘YTD’ and ‘All’ so I was stumped.
Back to Andrew where he had explained to me I needed to change my ‘Period All’ calculation from
> to >=
And that I didn’t need the show hide option on my filters box because we had already predefined it in the parameter calculation. Doh!
Below is the result of the calculation to make it work in its glory!
If this explanation does not make sense try and apply this calculation at the start without the YTD method using your own dates, measures to see if it works. Then build it up from the bottom adding YTD!
Good Luck – Happy Calculationing