4 Replies Latest reply on Jun 26, 2018 6:28 AM by Patrick Weber

# issue: date calculation between different intervalls

Hi Tableau friends,

I created a calculation (current_Intervall) which is able to show the start date of an intervall based on the current date (see my attached workbook).

The current date can be set up by parameter Test_date.

I think something is missing in my logic because e.g. Test_Date = June 26, 2018 then field "current_intervall" will show both start dates of June and July 2018.

In this case the next month (start_date= June 19) is correct. How I have to modify the logic that May 18 won't be shown?

current_Intervall:

```DATE((
IF [Test Date]>=[Start_Date] and [Test Date]<=[End_Date]  Then str(date([Start_Date]))
ELSEIF [Year]=DATEPART('year',[Test Date]) and [Month]=DATEPART('month',[Test Date]) and [Test Date]>=[Start_Date] and [Test Date]>[End_Date]
))
```

I think the issue appears if the current date is in the same month when another intervall already starts.

Any hints or tips?

• ###### 1. Re: issue: date calculation between different intervalls

Hi Patrick,

Try this? -

DATE((

IF [Test Date]>=[Start_Date] and

[Test Date]<=[End_Date]

Then str(date([Start_Date]))

ELSEIF [Year]=DATEPART('year',[Test Date]) and

DATETRUNC('month', [Test Date]) =DATETRUNC('month',[Start_Date]) and

[Test Date]>=[Start_Date] and [Test Date]>[End_Date]

))

Thanks,

Mavis

1 of 1 people found this helpful
• ###### 2. Re: issue: date calculation between different intervalls

Hi, Patrick

Please find my solution attached as well as below sample result for your reference.

Basically I think what you want to achieve is to get the first match period if the test date falls into two or more periods, so I did another test for 20 of April 2018

Hope this helps

ZZ

• ###### 3. Re: issue: date calculation between different intervalls

Thanks for your effort. I needed always the next interval where the current date fits in. But it was already solved in the post above.

• ###### 4. Re: issue: date calculation between different intervalls

Thanks that's exactly the function I was looking for.