Average daily sales per month

[Andvil]

Hello,

I have a report with company sales. I want to get the average daily sales by dividing the total month sales by the number of working days. For example, January might have 20 working days, February 19, March 21. 

I am thinking about using a DAX formula with "IF": Example> If month=January, then sales/20, else if month=February, then sales/19.... and so on until December.

Is there a faster way to do it?

Thank you for the help,

JALV

[amitchandak · Accepted solution]

@Andvil , You can have

Work Day = if(WEEKDAY([Date],2)>=6,0,1)

In your calendar and sum it up for every month

Avg MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))/ CALCULATE(SUM('Date'[Work Day]),DATESMTD('Date'[Date]))

[V-pazhen-msft]

@Andvil 

Basically you need to count the working days for each month. I would recommend you create a flag column to identify working days. And then use the column to find the total workings for respective month. 

flag column = IF(WEEKDAY([Date])<>6 && WEEKDAY([Date],2)<>7,1)
Total working days Column = CALCULATE(SUM('Table'[flag]),ALLEXCEPT('Table','Table'[Date].[MonthNo]))

Result = Sales/Total working days

IF you need measure:

Total working days measure = CALCULATE(SUM('Table'[flag]),FILTER(ALL('Table'),[Date].[MonthNo]=MAX([Date].[MonthNo])))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Andvil ,

please allow me to direct your attention to this article: https://www.daxpatterns.com/time-patterns/

This article covers almost everything necessary to create powerful time based calculations using DAX.

These kind of calculations become much more simple, if a dedicated calendar table is used to connect to the date columns in your fact tables.

Hopefully, this provides some additional ideas to tackle your challenges.

Regards,

Tom