4 Replies Latest reply on Jan 15, 2019 1:22 PM by Nicholas Vining

# Month over month calculation

Hi all,

I am new to tableau,

I need to calculate % difference between sales for every month across all the states in united states and need to have a month filter so that a particular month can be selected.

I did a quick table calculation for calculating the % difference.  However, i need to select min two months in the filter to get the % difference.

the result I am trying to achieve is when I select a particular month in the filter, I should be able to get the % difference between the selected month and the previous month.

for example, I need to select January in the filter and I should be able to get the % difference between January and December(last year) without having to select both January and December months in the filter. In other words, the filter should be single select.

I am attaching the workbook, Please let me know any possible ways to perform this task

• ###### 1. Re: Month over month calculation

I'm not sure what happened to my last answer to this one.

See attached.

When you quick filter, you eliminate rows from the table underlying your sheet.  If you pick January, then December is gone, and the LOOKUP to see the prior month has nothing to grab.

Use a table calc as your filter in these cases, and your underlying table remains intact.  A table calc only governs what is displayed, not what is brought in from the data source.

I made a LOOKUP calc to use as a filter.

• ###### 2. Re: Month over month calculation

Thank you, Joe,

Your solution works, and as you mentioned above Sort is a problem, I am trying the parameter approach and struggling to figure out that part. if possible can you help me with that

Regards

Nagarjun Srinivas

• ###### 3. Re: Month over month calculation

If you are always going to allow just one selection at a time, then a parameter approach would work.  (Currently, parameters are only single-select.)

For this example I just focused on MONTH.  If month AND year are necessary, in the [Select a month (copy)] calc I built, I have a commented section for one way you would also take year into consideration.

So inside my LOOKUP I embedded 'IF' logic.  Then set the values either to 1 or 0.  When I put that on the filter shelf, I select only for value = 1.

Your 'IF' logic can be as complicated as you need in a thing like that.  What matters is that you make the right conditions result in a value of 1.

See attached.

1 of 1 people found this helpful
• ###### 4. Re: Month over month calculation

Hi Nagarjun,

Building off of what Joe said, and more specifically addressing your question on how to calculate January compared to December of the previous year, here's a simple calc that should help!

IF

datepart('month',[date])>=[Month Parameter]

and datepart('month',[date])>[Month Parameter]+10

and [Month] = 1

and DATEPART('year',[date])<= [Year]

and DATEPART('year',[date])<= [Year]-1

OR

datepart('month',[date])<=[Month]

and datepart('month',[date])>=[Month]-1

and DATEPART('year',[date]) = [Year]

then 1

else 0

END

It's been almost a year so I hope you've already solved this, but I wanted to post it here in case anyone else was having issues as well.

Thanks,

Nick