Calculate average using only workdays excluding weekends/holidays

[AA622]

I need to calculate the average of an item by rolling 6 months usage but only during workdays. It has to exclude weekends and holidays

This is the formula I use to calculate the rolling 6 month usage:

UsageRolling6mo =

var numMonths = 6

var rollingsum =

    CALCULATE(

            SUM(TABLE[QTY]) *-1,

DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-numMonths,MONTH)

    )

Return

rollingsum

From there I grab the above measure and divide it by 180. This is the formula:

DIVIDE([UsageRolling6mo],180)

Obviously I know half of a year is 182.5 days.

First of all, is my rolling 6 month usage formula correct or is there a better way? I am havng some trouble matching up the measure vs the actual usage results.

Second, Does the rolling 6 month usage formula need adjusting for the workdays only requirement AND what is the best way to calculate the working days in a rolling 6 month period?

[Ashish_Mathur]

Hi,

It will help if you share data in an MS Excel workbook and show the expetced result there via formulas.  One can then translate those Excel formulas into DAX formulas.

[v-yueyunzh-msft]

Hi , @AA622 

Based on your description, you want to ask for the number of working days for a certain date range. Right?

You can try using the new NETWORKDAYS() function.

For more information , you can refer to the DAX document:

NETWORKDAYS function (DAX) - DAX | Microsoft Learn

Best Regards,

Aniya Zhang

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