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

    Calculate days per month / days per year

    Scott Schmeling

      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
          Hari Ankem

          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
            Ken Flerlage

            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),

            DATEADD('day', -1, DATEADD('month', 1, MAKEDATE(YEAR([Order Date]), MONTH([Order Date]), 1)))

            )+1

             

            See attached workbook.