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

# daily sales per month

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

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: I am attaching the solution using Sample Superstore dataset.

I hope this helps

Rodrigo

• ###### 2. Re: daily sales per month

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
• ###### 3. Re: daily sales per month

Amazing!