6 Replies Latest reply on Nov 1, 2018 6:48 PM by Shinichiro Murakami

# Is previous Month Calculation in a Date data table supposed to work

Tableau Desktop 2018.2.0

Hello guys

I need to create a report with following requirements (attached twbx) :

1- It has to be a data table

2- Daily basis

3- A whole month shown as a day list

4- Availability of filter between different months

5- A dynamic view to select between 2 Measures (Sales and Previous Month Profit)

I have developed all of the requirements successfully but the number 5, specifically Previous Month Profit. Most of the formulas I've tried does not work with filtered dates, like for example:

Previous Month Profit =

"lookup(sum([Profit]),-30)"

This only works when I have 2 months in the view, otherwise does not show anything, as per the requirement number 3, the report can only show 1 month. The same thing happens with other formulas like Datepart, Datediff, dateadd, etc.

So in summary the question I got is: How to show Previous month Profit and fullfilling all the requirements previously mentioned.

Any comment or question will be appreciated.

Best Regards

• ###### 1. Re: Is previous Month Calculation in a Date data table supposed to work

HI Diego,

Typical solution for this is below.

https://kb.tableau.com/articles/howto/filtering-view-without-filtering-underlying-data

Thanks,

Shin

• ###### 2. Re: Is previous Month Calculation in a Date data table supposed to work

Hi Schinichiro

Thanks for your support, will take a look at it and let you know

• ###### 3. Re: Is previous Month Calculation in a Date data table supposed to work

Hi Schinichiro

I see in your twbx that effectively Previous Month Profit is now shown but got a couple of questions:

For 01/03/2014 Profit is -\$209 while Previous Month Profit is shown as -\$6. This should means that Profit for 01/02/2014 is -\$6, but when I change the selector of Month LookUp to Feb 2014 then the Profit for the first day of the month is \$206

Am I missing something? Or, what is been shown in Previous Month Profit?

Best Regards

• ###### 4. Re: Is previous Month Calculation in a Date data table supposed to work

I did not take care of your other calc.

It is not referring exact one moth ago, just 31 record item before.

This is completely different request and not at all easy fix.

Thanks,

Shin

• ###### 5. Re: Is previous Month Calculation in a Date data table supposed to work

That is right, since the table is on daily basis, I used an offset of 31 records that more or less would be a month.

Ok, will create a new and better explained thread

Thanks a lot!

• ###### 6. Re: Is previous Month Calculation in a Date data table supposed to work

HI Diego,

The post is supposed to be reviewed by all community people, and want to close this anyways.

Could you mark my answer as correct to close the thread, not from inbox but from original post, while creating another post.

Thanks,

Shin