3 Replies Latest reply on Feb 9, 2017 1:49 PM by Jim Dehner

# Calculating prior month when crossing calendar years

I have a workbook that contains company wide metrics that is setup to provide the current and prior month numbers based on file date parameter that is set for the month I choose. I have an issue calculating prior month when crossing calendar years. Nothing populates for the prior month, December 2016, now that the current month is January 2017. We're using an annualized calculation.

For the current month -

For the prior month -

I know this issue is tied to the use of month for the file date parameter, but I haven't been able to successfully include year as part of the calculation.

I am not able to share a packaged workbook as the data included is too sensitive. I'm hoping the calculation will be enough to garner a response since I'm looking to find a way to point the data in the prior year when the current month is January 2017 and prior month is December 2016.

• ###### 1. Re: Calculating prior month when crossing calendar years

Hi Rebecca

I had to deal with something similar and found the only way around was to check the month and the year with and IF / And statement

if [Period]='m'

and

DATEPART('month',[date])=

datepart('month',[date parameter] )

and

DATEPART('year',[date])=

datepart('year', [date parameter])

then[Sales]

end

Not sure what else you have in your dimensions that you can check year against

Hope it helps - even a little

Jim

1 of 1 people found this helpful
• ###### 2. Re: Calculating prior month when crossing calendar years

To share sensitive data, anonymize your data.  See this 10 minute video that shows how.  We may need to see a workbook at some point, so I recommend that you start working on this:

Where are you generating your [... current ...] and [... prior...] fields from?

You can just store your data with date values in the rows, and Tableau's magical date handling can do the math for you.  And tableau doesn't care about year boundaries.  So there are myriad ways you can get last month's data right from the data as it comes in from your data source.

For instance, DATEADD('month',-1,[date field]) will subtract a month.  Doesn't matter if it is July or January.  Doesn't matter if the field is a fiscal date or calendar date.

If your sheet is set up with monthly cells, LOOKUP(<whatever>, -1) will grab <whatever> from the prior month.  Again, no problem with year boundaries.

2 of 2 people found this helpful
• ###### 3. Re: Calculating prior month when crossing calendar years

Thanks Joe - that would have been much easier than what I did

Jim