Assistance Needed: Calculating Average Duration per Month in Database with DAX Code

Hi bdeleur ,

Please try below steps:

1. add a new column with below dax formula

Duration = DATEDIFF([start date],[end date],MINUTE)

2. create two measure with below dax formula

Total Duration by Product = 
   SUMX(
       SUMMARIZE(
           'Table',
           'Table'[Reference],
           'Table'[department_name],
           "TotalDuration", SUM('Table'[Duration])
       ),
       [TotalDuration]
   )

Average Duration per Month =
VAR cur_re =
    SELECTEDVALUE ( 'Table'[Reference] )
VAR cur_dt =
    SELECTEDVALUE ( 'Table'[department_name] )
VAR _a =
    MONTH (
        CALCULATE (
            MAX ( 'Table'[start date] ),
            FILTER ( ALL ( 'Table' ), [Reference] = cur_re && [department_name] = cur_dt )
        )
    )
VAR _b =
    FILTER (
        ALL ( 'Table' ),
        [Reference] = cur_re
            && [department_name] = cur_dt
            && MONTH ( [start date] ) = _a
    )
RETURN
    AVERAGEX ( _b, [Total Duration by Product] )

Please refre the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous ,

Tnx for your answer. When I look at the outcome, I see that it does indeed nicely take the average. However, it takes the average of the multiple rows per reference. I would like it to look at the total sum and then calculate the average per month from this total sum. For example, reference 1 has 2 rows: 19 and 64 minutes. Together, this is 83 minutes. In the average measurement, this becomes 41.5. But I want the average over the month from the sum across all references.

And if I apply this code to my large file with multiple years and multiple departments, I get 'gaps' in my overview per month/year.