2 Replies Latest reply on Nov 14, 2018 6:52 PM by Ken Flerlage

Calculate days per month / days per year

Good afternoon,

Is there a quick formula in Tableau to calculate days per month / days per year?

Basically I want it to tell me:

January: 31 Days

February: 28 Days - 29 Days on leap years

March: 31 Days

April: 30 Days

May: 31 Days

etc...

Years:

2016: 366

2017: 365

2018: 365

I want to create a dimension with this calculation- so when I List Months on Rows- I can list this dimension next to it- so the client knows whether we are in leap year or not.

Thanks,

Scott

• 1. Re: Calculate days per month / days per year

Are you going to give the user the option to select/specify the year for which you want to know the number of days in a month/year?

If no, does you data have a year column based on which you want to know the above data?

• 2. Re: Calculate days per month / days per year

You'll need to have some dates in your data set (at least one for each month) for this to work. I used the Superstore data set and created the following calculated fields:

Days in Year

// Get the first day of the year, then the last, then calculate the difference

DATEDIFF('day',

MAKEDATE(YEAR([Order Date]), 1, 1),

MAKEDATE(YEAR([Order Date]), 12, 31)

)+1

Days in Month

// Get the first day of the month, then the last, then calculate the difference

// We get the last day by: 1) Getting the first day, 2) Adding one month, 3) Subtracting 1 day.

DATEDIFF('day',

MAKEDATE(YEAR([Order Date]), MONTH([Order Date]), 1),