1 Reply Latest reply on Jan 28, 2013 11:08 AM by Catherine Rivier

MTD/YTD Calculations

Hello ---

Looking for some advice on how to calculate MTD & YTD results for a fiscal calendar that begin's on 2/3/2013. My data source has Sales Date, Fiscal Week, Fiscal Month and Fiscal Year. I'm simply looking to sum up values for each of these time periods into a Tableau data table.

Unforunately I can't attach the actual data as it's confidental data.

Any ideas how I'd create this?

Thanks!

• 1. Re: MTD/YTD Calculations

Hi,

Just FYI, in the future when posting, you can always use the Tableau sample data to recreate your problem

I'm working through this by first assigning the "correct" Fiscal Year, Month, and Week values to each actual date - and from this, the YTD and MTD type of calculations should work as normal.

So, the way I'm tackling this problem is to trick Tableau into thinking that Feb 3rd is the start of the Fiscal Year, and making some other adjustments from this.  It's the same method to get Fiscal Weeks starting on a Monday instead of a Sunday, for example.

I used the Superstore Sales sample, attached.    I am using field Order Date, which has most dates between 2009 and 2012, as the date to modify.  The final 4 columns show a Fiscal Year, Month, and Week.

I made 4 new calculations, and have them in the view in various ways to show how this will work:

Field: "PART 1 - Fake Order Date"

[Order Date]-33

This makes Feb 3rd of any year behave like Jan 1st.

Field: "PART 2 - FY Text"

'FY'+STR(DATEPART('year',[PART 1 - Fake Order Date]))

This assigns year "FY 2012" to all dates between Feb 3rd 2012 and Feb 2nd 2011, and so forth.  This is how I'm using the fake Jan 1st date above.

Field: "PART 3 - Fiscal Month Text"

DATENAME('month',[Order Date])+', FY'+DATENAME('year',[PART 1 - Fake Order Date])

On this I'm assuming when you want to label Fiscal Month, you might want to label it like 'December, FY2012'.  You might have had something else in mind.

Field: "PART 4 - Fiscal Week Text"

'Week '+DATENAME('week',[PART 1 - Fake Order Date])+', FY'+DATENAME('year',[PART 1 - Fake Order Date])

This takes the first week of the year and marks it as Week X, FYxxxx.  Again, I'm making assumptions on what you were looking for.  This one can get even more tricky, depending on your requirements.  For example, right now it is using the week separation of Jan 1st instead of Feb 3rd, so if Feb 3rd was a Tuesday but Jan 1st was a Thursday (like in 2009), the week will run Friday through Thursday.

You can see in my view, the last 4 columns have the fiscal year, month, and week listed.  I've guessed at what you'd want to see and how you'd want to define these.  Once these are set, you can use these as you normally would to calculate things like YTD or MTD.

I'm guessing there might be more to come on this, and that's completely fine.  I'm making simple assumptions on how you would define things like 'Fiscal Week'.  If this needs a little further help, take a look at the sample data and give a little more detail on what dates go in what fiscal groups.  I'm happy to help!

Catherine