How to get the MTD Sales average (include all days' sales) but dividing by only business days.

[Anonymous]

I need to calculate the MTD Sales average per working days, but including in the calculation all sales (weekdays and weekends).

[Anonymous · Accepted solution]

Hi @Anonymous ,

It's easier to calculate MTD with DAX. Here's a solution using DAX.

1.Create a calendar table and create a relationship with the main table.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "Weekday/Weekend", IF ( WEEKDAY ( [Date], 2 ) <= 5, "Weekday", "Weekend" )
)

2.Create two measures. The first one is to get the MTD sales and the second one is to get the number of weekdays.

MTD Sales = TOTALMTD(SUM('Table'[Sales]),'Table'[Date])

MTD Weekdays Count = 
TOTALMTD (
    CALCULATE (
        COUNT ( 'Calendar'[Date] ),
        FILTER ( 'Calendar', [Weekday/Weekend] = "Weekday" )
    ),
    'Calendar'[Date]
)

3.We have the sales and the number of working days, and we just need to divide to get the average.

Average = DIVIDE([MTD Sales],[MTD Weekdays Count])

Here's the result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

We need to see your data. Also do you need a Power Query solution or DAX solution.

How to Get your Question Answered Quickly - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523