4 Replies Latest reply on Aug 21, 2013 3:44 PM by yingyingchen0

# How to show month to date % change base on the max. date selected?

I am trying to show the month to date % change in my Tableau workbook. the date for comparison would be decided based on the filter.

Say if the date range is 7/1-8/6, then MTD% would be sum(8/1-8/6)/sum(7/1-7/6) - 1; if date range is set as 7/1 - 8/5, then MTD% would be sum(8/1-8/5)/sum(7/1-7/5) - 1 = 25%

I have tried to do this if I am using now() or today() and it works, however, since I don't know the difference days between today and the max date in the worksheet, it just didn't work for me. Can anyone help?

This workbook will be uploaded to the server and new data will be added day by day. So i can't set the end date using parameter.

• ###### 1. Re: How to show month to date % change base on the max. date selected?

Yingying, a couple of us have looked at this and we're really not sure what you're trying to do.

I have tried to do this if I am using now() or today() and it works,

In the workbook you attached I am not seeing any "working" % difference. Please add a sheet to the workbook showing this working, then maybe we'll better understand what you're trying to accomplish. Thanks,

--Shawn

• ###### 2. Re: How to show month to date % change base on the max. date selected?

Hi Shawn,

I don't want to confuse you. using today() or now() is NOT working. using today() or now() can only give me % different between a fixed day and the days in previous months. What I need to do is; the user needs to be able to control the date they want to compare, say if they pick 8/6, they can see % different of sum(8/1-8/6) vs. sum(7/1-7/6). if the users pick 8/5, they can see % different of sum(8/1-8/5) vs. sum(7/1-7/5).

as the data will be updated each day, so I can't use parameter as parameter wont' refresh even it has one more day's data in the workbook.

• ###### 3. Re: Re: How to show month to date % change base on the max. date selected?

Yingying, it sounds like you are really looking for a dynamic parameter, which is the #1 feature request in the ideas section. So that's not an option. However if you can live with your user typing in the Maximum Day of the Month as an integer, then you can get the % difference you're looking for (see attached). Here's the calculation:

IF DATEPART('day', [date] )<=[Max Day of Month] THEN  [KPI] END

Hopefully this will work for you.

--Shawn

EDIT: That was the wrong workbook. I've attached the correct one now.

• ###### 4. Re: How to show month to date % change base on the max. date selected?

Thanks Shawn, This will fix the problem somehow. however, the users always get confused as they don't know how to set up the max. date as sometimes the workbook failed to update, the max. date is not necessary to be yesterday.