6 Replies Latest reply on Apr 19, 2018 10:14 AM by Sonali chavan

# How to calculate the days if not calendar month

Hi Everyone,

I am working on a report in which I have to provide the report monthly according to contract month.

I was using this formula to get the details but unfortunately its not working.

I am working with tableau server in which I fetch data from Jira, from there I have to calculate all PDs consumed in a month according to services/applications.

Anyone can help me how to get the correct formula.

M1=Contract Month 1

15-xxx = Month start date.

14-xxx = Month end date.

IF DATE(2017-11-15) BTEWEEEN (2017-12-14) THEN M1

END

• ###### 1. Re: How to calculate the days if not calendar month

Hi Prashant, could attach a packaged workbook with some sample data to work with?

I'd like to know if fields Month Start Date and Month End Date are user selected? If so, you'd need parameters in combination with calc field.

• ###### 2. Re: How to calculate the days if not calendar month

Hi Prashant,

Without knowing exactly what you're trying to do, I would suggest something like the following:

IF DATEPART('day', [date variable]) >= 15 THEN

DATEPART('month', [date variable]) - DATEPART('month', [contract start date]) + 1

ELSE

DATEPART('month', [date variable]) - DATEPART('month', [contract start date])

END

Another function to consider is DATEDIFF.

If these don't help, it would be helpful to consider posting a workbook with your data.

Best,

Jeff

• ###### 3. Re: How to calculate the days if not calendar month

Apparently, these are the dates I have and I have to group that in several months which are not calendar months.

Each month starts on 15th of every month and ends on 14th

• ###### 4. Re: How to calculate the days if not calendar month

Apparently, these are the dates I have and I have to group that in several months which are not calendar months.

Each month starts on 15th of every month and ends on 14th

• ###### 5. Re: How to calculate the days if not calendar month

Prashant,

You mean to say is, Start of the month is 15, say for e.g. 15/4/2018 to 14/5/2018 is considering as April month, 15/5/2018 to 14/5/2018 is May and so on.

And you need to show data for a specific month, as per above understanding, am  I correct?

Sonali

• ###### 6. Re: How to calculate the days if not calendar month

Hi Prashnat,

I hope this solves your query, attached is screenshot below:

1. Date is your original date.

2. The new month is the calculated field which will calculate the start of the month from 15 to 14 .

Formula:   IF DAY([Date])<15 then DATEADD('day',-15,[Date])ELSE [Date] END

As per above formula 15 Jan to 14 Feb, it's giving me January and so on.

3. I have used month just to check if an above formula works or not.

Hope

Sonali