Forum Discussion

AA622's avatar
AA622
Helper II
3 years ago

Calculate average using only workdays excluding weekends/holidays

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?
Need Help

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.