10 Replies Latest reply on Sep 28, 2015 11:27 PM by JayC

# Using Previous Month Figures in calculated field

Hi,

I would like to fetch previous month number in my case Aug'15 for a particular metric say for sales from an existing worksheet and use that particular figure in calculated field.

Note: number is dynamic and changes as per filter

Have tried below-

OR

But has not worked.

Is there a way to achieve this?

• ###### 1. Re: Using Previous Month Figures in calculated field

Hi Jayachandra,

Create a calculated field with this formula:

```IF YEAR([Your Date Dimension])=YEAR(NOW()) AND MONTH([Your Date Dimension])=MONTH(NOW())-1 THEN [Your Measure] END
```

I hope this helps

Best

Ramon

• ###### 2. Re: Using Previous Month Figures in calculated field

Unlike Excel, which is a cell-based software, Tableau does not 'fetch' values from other worksheets cells. That's not to say you can't do what you're wanting to do, it's just that you need to do it all in one sheet. Meaning each time you want to use SUM(Sales) you just drag sales out onto the current sheet, not go look it up on another sheet.

You're probably looking for something like this using Superstore:

IF DATETRUNC('month', [Order Date]) = DATETRUNC('month', DATEADD('month', -1, TODAY()))

THEN SUM([Sales]) END

Does that work for you?

• ###### 3. Re: Using Previous Month Figures in calculated field

Ramon,

I have tried your formula and used it as per requirement  but it did not work for me.

1038 is the number i wanted to pick and was expecting only that number to be isolated from the list of other numbers

• ###### 4. Re: Using Previous Month Figures in calculated field

Hi Shawn,

I have faced an error dialog while integrating your formula. any idea on how to deal with this please -

• ###### 5. Re: Using Previous Month Figures in calculated field

Yeah sorry I hadn't clicked Apply yet before copy/paste. You need to get rid of the SUM() after the THEN. When you drag it out into the viz it will sum it.

• ###### 6. Re: Using Previous Month Figures in calculated field

Shawn,

Formula field was created but still the result is not as desired.

did this work on samplestore data. can you please attach the same if you have one please. Result is displayed below

• ###### 7. Re: Using Previous Month Figures in calculated field

It seems I'm not understanding your situation or what you want to reach.

I need more information in order to give you a proper solution. Could you describe more want you want to accomplish and if possible a workbook with sample data set.

best

Ramon

• ###### 8. Re: Using Previous Month Figures in calculated field

Create this calculation: DATETRUNC('month', [Order Date]) = DATETRUNC('month', DATEADD('month', -1, TODAY())) and put it on the filter shelf. Then put SUM(Customers R12) on the label shelf. What do you get now?

• ###### 9. Re: Using Previous Month Figures in calculated field

Jay,

It would be helpful to see exactly what you're trying to accomplish.  Between this post and this one:  formula for using previous month for particular measure I think we've answered what you're asking for.

If you can't share exactly what you're doing then mock something up using superstore data.  Then post that twbx workbook here so we can see what it is you need.

• ###### 10. Re: Using Previous Month Figures in calculated field

Dan & Ramon,

Thanks for all your time and support. Shawn's formula from the above post worked for me. Thanks a lot Shawn.

Dan & Ramon this is what i was trying to achieve- Pull out latest month number for selected measures. Apologies for not being clear in posting my requirement and once again i thank you all for your invaluable time.

Thanks again!