7 Replies Latest reply on Dec 29, 2016 9:36 AM by Helen Rowson

# Financial YTD and Rolling 12 Month calculations

Hi,

I'm relatively new to Tableau.  I want to create a calculation that depending on the month selected in the filter will bring back a financial YTD position.  For example, if you select October 2016, you'd get a sum of April 2016 to October 2016.  Similarly I also want to create a calculation that gives a rolling 12 months, so if you select October 2016 on the filter, it would give you the sum of November 2015 to October 2016.  An example of the calculation that gives the number for one month for infection type MSSA is:

sum (if[Limit]='Trust Apportioned' and [Infection_Type] = 'MSSA' then [Measures_Value] END)

Screenshot of Tableau screen attached.

• ###### 1. Re: Financial YTD and Rolling 12 Month calculations

Hi Helen,

I'm not completely sure of how your data looks like but perhaps this 2 articles will give you some guidance:

Creating YTD and MTD Calculations | Tableau Software

Calculating a Rolling 12-Month Value with DATEDIFF() | Tableau Software

Both use data functions that you can use along other calculations or the measures in your dataset.

Hope this helps!

• ###### 2. Re: Financial YTD and Rolling 12 Month calculations

Thanks Santiago, I think I have explored a similar post to this. The calculation in the first workbook link you gave is the right principle but instead of 'today' I want it the YTD calculation to refer to whatever month is selected in the month filter I have on the workbook. So, if my filter said September 2016, then the financial YTD calculation would sum the monthly figures for April 2016 to September 2016.

In the example it says: IF  <= TODAY()

AND DATEDIFF('year',[Order Date],Today())= 0 THEN  END

Any ideas?

Many thanks for responding to my post.

Helen

• ###### 3. Re: Financial YTD and Rolling 12 Month calculations

Hi Helen,

No worries! I think you can achieve this using Parameters. You can create a Parameter that would allow you to pick a month:

You can then replace TODAY() on the original formula with that parameter:

Note I'm also using another parameter for 'Starting Month', this is just to make the calculation start in April. If this month doesn't change, you could just replace it with something like #04-01-2014#.

An example is attached, but data only goes up to 2014 so you may need to adjust the years

Best,

Santiago

• ###### 4. Re: Financial YTD and Rolling 12 Month calculations

Thanks so much.  I will give it a go when I'm next working in Tableau..... Will be great if it works for my dataset!

Thank you,

Helen

Financial YTD and Rolling 12 Month calculations

• ###### 5. Re: Financial YTD and Rolling 12 Month calculations

Hi Santiago, thank you for your reply.... do you have the workbook in an earlier version of Tableau please?  I'm working in Tableau 10.... not to worry if not... thank you for your help,

Helen

• ###### 6. Re: Financial YTD and Rolling 12 Month calculations

No worries. Here's the workbook on version 10.0. Happy New Year!

• ###### 7. Re: Financial YTD and Rolling 12 Month calculations

Thank you very much. Happy New Year to you too!

Best wishes.