Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average Jobs Logged by Working Days

Hello im trying to work out the average jobs logged per working day via quick measure but im not sure how to do this? Is there an easier dax forumla to work this out please? Ideally only to measure working days - Monday-Friday

 

Task Created - Is the dates

Task ID - Is the jobs

 

Thanks, 

Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Work day = 
Var _weekday=WEEKDAY(SELECTEDVALUE('Table'[Task Created]),2)
Var _workday=IF(_weekday<6,1)
Return _workday
    Average = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Task ID] ),
        FILTER ( ALL ( 'Table' ), [Work day] = 1 )
    ),
    SUMX ( VALUES ( 'Table'[Task Created] ), [Work day] )
)

    First determine which days are working days. The numerator is the number of Jobs added on a weekday, the denominator is the number of weekdays, calculate the average number of Jobs on a weekday, and whether this is the result you want?

     

    Best Regards,

    Community Support Team _Charlotte

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , You can get working days between two dates like

     

    new column

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

     

    new measure

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Task Created]),Max(Table[Task Created])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the fast responce! I'm still getting an error? Im not very good with Dax at all. Is there a way to use the quick measure? 

       

      Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR('Reactive - Date Raised - Master'[Task Created]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Work day = 
Var _weekday=WEEKDAY(SELECTEDVALUE('Table'[Task Created]),2)
Var _workday=IF(_weekday<6,1)
Return _workday
    Average = 
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Task ID] ),
        FILTER ( ALL ( 'Table' ), [Work day] = 1 )
    ),
    SUMX ( VALUES ( 'Table'[Task Created] ), [Work day] )
)

    First determine which days are working days. The numerator is the number of Jobs added on a weekday, the denominator is the number of weekdays, calculate the average number of Jobs on a weekday, and whether this is the result you want?

     

    Best Regards,

    Community Support Team _Charlotte

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