Elapsed Number of Days in Month, QTR, Year Dynamically


To get the number of days elapsed in the current month create the following calculated field:


                    DATEDIFF('day',DATETRUNC('month',today()),today())
                     

To get the number of days elapsed in the current quarter create the following calculated field:


                    DATEDIFF('day',DATETRUNC('quarter',today()),today())
                     

To get the number of days elapsed in the current year create the following calculated field:


                    DATEDIFF('day',DATETRUNC('year',today()),today())
                     

Total Number of days in any given month

To get the number of days in the current month dynamically:


                    DATEPART('day', DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month', [Date]))))
                     

Some uses: Track goals and targets. For instance, let's say your sales goal is $10,000 per month. If you are 23 days into the month then you can use the formulas above to see where you should be in terms of the number of days that elapsed in the month.

Some tips:

  • Aggregrate the measure as a minimum not a sum
  • Change to discrete to show numeric values or drag to text
  • This will depend on the filter context

The latter calculation depends on the filter context which is a fancy way of saying it depends on what dimensions/measures are in the row or column shelf in tableau.





Services
  • SEO
  • Online Advertising
  • Online Ordering For Restaurants
  • Data Analytics

  • External Resources 4 Developers
  • CSS Tricks
  • MDN

  • Dash-Intel.com is a Power BI and Tableau resource site around data visualizations and building business dashboards.

    Data Analytics consultants in Edmonton, Alberta specializing in Tableau and marketing analytics

    Copyright 2020 Dash-Intel.com Terms / All rights reserved.