3 Replies Latest reply on Feb 20, 2018 5:12 AM by Jim Dehner

# Calculations on Previous month data

Hi Team,

I have view like below, where i need to show Recent 6 months sales, Goal, Rate and other measures based on the month and year selection.

Below are the calculations.

Goal = [Rate]*([Sales])

Rate  should be of previous month's.

for example Goal for August = July Rate * (sales)

= 61*63121 (should be like around 3850381).

Can any one help me how to get the previous month data for every month and calculate Goal?

And also  i need show QTD, PQTD and QTD Variance in the same view.

Regards,

Amala

• ###### 1. Re: Calculations on Previous month data

Good morning

I have 2 solutions for you - one using your original formulas and a second that I modified

first using yours

and it returns this

but I have a question regarding your rate calculation -

usually in calculating rates the measures are aggregated - see below

then the goal formula becomes

now that is going to produce very different results

you know your data and analysis - just looked a little different - you can go either way

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculations on Previous month data

Thank you JIM for the reply.

I already tried with look up. it will work fine but it will not give any value for first month on the window.

In the above example, it is showing empty. But i need to show the value for July also.

And About Rate calculations, i just gave you an example with sample data. But i am doing summation in actual data.

Can you please help me how to get the data for July month also with same logic(Previous Month Rate * sum(Sales)).

Regards,

Prajwal

• ###### 3. Re: Calculations on Previous month data

see the attached

the reason that july did not show up is that you had a filter to limit the data to 6 month - Tableau needed the preceding month to make the calculation

(don't worry I'll show you how to get the first month out of the viz)

Start by changing the the formula for the filter as shown below

add the filter to Context (right click the filter and select add to context)

That will get you the viz below with june showing - to hide the first month (it will adjust automatically as you update the viz)

right click the month and select hid - see below

you end up with this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.