1 2 Previous Next 21 Replies Latest reply on Mar 14, 2018 3:51 PM by Matheus Barros

# Current month and previous 6 month calculation

I need help calculating current month, previous month and previous year but I don't have a date field. There are 2 different fields - one for month and the other for year and therefore can't use any date functions. Any help would be appreciated!

Message was edited by: Priyanka Agrawal

• ###### 1. Re: Current month and previous 6 month calculation

Hi Prouanka,

You can create a date field if you combine the month and year fields. Have you got a package workbook?

Convert your Month and Year Fields to a string and use this calculated field if you don't have a day:

DATE("01" + " " + [Month] + " " + [Year])

Then right-click you new calculated field and select change data type to a date.

Kind Regards,

Stephen

1 of 1 people found this helpful
• ###### 2. Re: Current month and previous 6 month calculation

Thanks Stephen, this really helped! I need to calculate current month, and previous year in a table. I am getting stuck when it comes to Jan'16 to Dec'15. Any ideas!

• ###### 3. Re: Current month and previous 6 month calculation

What I like to do is number my months based on the latest date in my data set. I use this calculation:

DATEDIFF('month',TODAY(),([Date]))

This will number your months going from 0, -1, -2, -3 etc. So for example from today:

March 2016 = 0

February 2016 = -1

January 2016 = -2

December 2015 = -3

and so on

This calculation dynamically changes as it's based on today's date so last month will always = -1. You can use a parameter if you like instead of TODAY() in the calculation to fix a specific date.

So say you want to look at last month Sales you would create a calculation that looks like this:

IF [Month Number] = -1 THEN [Sales] END

If you want to calculate your previous 6 months Sales you would use this:

IF [Month Number]>=-6 and [Month Number]<=-1 THEN [Sales] END

This numbering system is very handy as you can create a rolling year calculation:

IF [Month Number ]> -12 then "Current Year"

ELSEIF [Month Number] > -24 then "Previous Year"

END

Similarly you can use numbering to identify previous years using this calculation:

DATEDIFF('year',DATETRUNC('year',TODAY()),DATETRUNC('year',[Date]))

So for today's date (it being 2016) this returns:

2016 = 0

2015 = -1

2014 = -2

and so on

Therefore the previous year always = -1. So for example, if you wanted to know your sales for the previous year you would use:

IF [Year Number] = -1 THEN [Sales] END

Hope this helps!

Kind Regards,

Stephen

4 of 4 people found this helpful
• ###### 4. Re: Current month and previous 6 month calculation

This was extremely helpful! Thank you so much for explaining this.

What I am trying to achieve is -

I have 2 parameters  one - Year and second - Month. So if I select Month = Jan and Year =2016 then in one row I want to see 2 columns -

Value for Jan 2016 , value for Dec 2015.

Now since I am using a Year parameter (as it is single select), I am unable to get value for Dec 2015 and if I put a condition ( Year <= Year Param) then I get all the values from the beginning. I want to get just one month prior value.

I have tried all the tricks available online but I still can't get the desired o/p. Can you please help me with this.

Thank you!

• ###### 5. Re: Current month and previous 6 month calculation

Also, I do not want to use Today () in any of the calculations.

• ###### 6. Re: Current month and previous 6 month calculation

Can you upload a package workbook? It will be easier for me, and others, to see how to help.

Thanks,

Stephen

• ###### 7. Re: Current month and previous 6 month calculation

Here is the tableau file - I want to display only current month and previous 1 month value for a selected year and month.

Currently, I have one sheet for previous month and another one for current month. The current logic works for all months except for the criteria where Current Month/Year = Jan'16 and Previous Month/Year = Dec'15.

Let me know what can be done to solve this!

Thanks a lot.

• ###### 8. Re: Current month and previous 6 month calculation

Hi,

I think I've done what you're looking for. See the attached workbook.

Kind Regards,

Stephen

1 of 1 people found this helpful
• ###### 9. Re: Current month and previous 6 month calculation

Stephen the file doesn't open on my laptop. I have tableau 9.1 on my laptop. Can you send it again, please! Thanks a lot!!!

• ###### 10. Re: Current month and previous 6 month calculation

I am guessing your version is different that mine. If you cannot save as 9.1 then can you paste the logic here, whatever works for you! Thanks a lot once again!

• ###### 12. Re: Current month and previous 6 month calculation

See attached.

• ###### 13. Re: Current month and previous 6 month calculation

You're hurting yourself with all the string variables.

I made INT variables for year and month.

And I made INT parameters for year and month.

You don't have enough data to show both previous month AND previous year at the same time, but the logic in the calcs will work when enough data is in there.

I wrapped the previous year and previous month calcs in the ZN() function so that at least you get zero when no data is there.

In the previous month calc, you have to subtract 1 from the year if you go backward from January to December.  You'll see logic in that calc for that.  I could have incorporated the [Month I want] calc into the [get previous month] calc the same way I subtracted a year, but I broke it apart to show the methodical progression of steps you need to do there.

• ###### 14. Re: Current month and previous 6 month calculation

Thanks Joe for your help! This is helpful but  doesn't help me achieve what I am looking for. I am looking to display RM_ID, Month_Desc, Previous Value and current Value. I want to use Year and month parameters to filter and show just the selected values.

If we select year param = 2016 and Month param = Jan then display all RM_IDs which have Jan'16 value and Dec'15 value. I am using these 2 parameters to show current data in my other sheets as well so want to be consistent all across. Thanks once again for all your help!!!

1 2 Previous Next