3 Replies Latest reply on Jan 27, 2019 7:21 PM by Rodrigo Calloni

    daily sales per month

    Alex Martino

      I have a bar chart with total sales per month. How can I create a formula that calculates the daily average within those months? The daily average should be regardless of whether there were sales on a certain day or not (because sometimes I think the average only looks at days present).

        • 1. Re: daily sales per month
          Rodrigo Calloni

          Hello Alex

           

          I think the tricky part on your request is that you want the whole month and if there are missing days in the dataset it gets pretty hard to count them in Tableau.

           

          You can start by creating a calculation just to set these days:

           

          [Days on Month]

          IF DATEPART('month',[Order Date]) = 01 THEN 31

          ELSEIF DATEPART('month',[Order Date]) = 02 THEN 28

          ELSEIF DATEPART('month',[Order Date]) = 03 THEN 31

          ELSEIF DATEPART('month',[Order Date]) = 04 THEN 30

          ELSEIF DATEPART('month',[Order Date]) = 05 THEN 31

          ELSEIF DATEPART('month',[Order Date]) = 06 THEN 30

          ELSEIF DATEPART('month',[Order Date]) = 07 THEN 31

          ELSEIF DATEPART('month',[Order Date]) = 08 THEN 31

          ELSEIF DATEPART('month',[Order Date]) = 09 THEN 30

          ELSEIF DATEPART('month',[Order Date]) = 10 THEN 31

          ELSEIF DATEPART('month',[Order Date]) = 11 THEN 30

          ELSEIF DATEPART('month',[Order Date]) = 12 THEN 31

          END

           

          You can then set the default aggregation for this field to be an Average.

           

          Next you can create a simple calculation:

           

          [Average per Month]

          SUM([Sales])/AVG([Days on Months])

           

          Now you can add this to text and it should display the average on the bar ends:

           

          Screen Shot 2019-01-27 at 9.39.20 PM.png

           

          I am attaching the solution using Sample Superstore dataset.

           

          I hope this helps

          Rodrigo

          • 2. Re: daily sales per month
            Paul Wachtler

            Hi Alex,

             

            If you want to programmatically count the number of days in a month, regardless of days in your data, you can use this formula:

            DAY(DATETRUNC('month',DATEADD('month',1,[Order Date]))-1)

             

            That will add a month to a date, then datetrunc it (which takes the first day of that month), then subtracts one day from that - so it's the last day of the month of the original date.  Then it takes the day value of that, which is the total number of days in that month.

             

            If you name that calculation Total Days then you create this second calculation which will give you your averages for each month:

            sum([Sales]) / max([Total Days])

             

            Best,

            Paul

            1 of 1 people found this helpful