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

[bdeleur]

Good afternoon,

I'm looking for help. I'm trying to perform a sum in my database, but here's my problem: it's not working 😊

The database consists of multiple rows, each movement of the product (reference) is displayed in a row with a start and end date/time. So, you can calculate the duration per line. Now, it's possible that the product appears multiple times in one department; this is an internal movement.

Now, I want to calculate an average per month of the duration. However, I need the sum per product (reference), and I want to report this by department. So, it would be great if this could be specified in the DAX code.

The example is for one department, but you can name the column "department." Different departments A, B, etc. The table below is a small extract, it shows what the outcome should be when you sum up the duration by reference.

Reference	department_name	start date	end date	Duration
1	A	19-5-2023 18:24	19-5-2023 18:43	0:19
1	A	19-5-2023 18:43	19-5-2023 19:47	1:04
2	A	3-5-2023 19:15	3-5-2023 19:19	0:04
2	A	3-5-2023 19:19	3-5-2023 21:18	1:59
2	A	3-5-2023 21:18	3-5-2023 21:22	0:04
3	A	11-5-2023 07:15	11-5-2023 09:56	2:41
3	A	11-5-2023 09:56	11-5-2023 10:17	0:21
4	A	1-5-2023 15:34	1-5-2023 17:09	1:35
4	A	1-5-2023 17:09	1-5-2023 18:05	0:56
4	A	1-5-2023 18:05	1-5-2023 18:08	0:03
5	A	1-5-2023 01:43	1-5-2023 01:50	0:07
6	A	1-5-2023 09:20	1-5-2023 10:43	1:23
7	A	1-5-2023 00:08	1-5-2023 03:10	3:02
8	A	1-5-2023 00:45	1-5-2023 01:59	1:14
8	A	1-5-2023 01:59	1-5-2023 02:04	0:05
9	A	1-5-2023 02:00	1-5-2023 02:39	0:39
9	A	1-5-2023 02:39	1-5-2023 02:45	0:06
10	A	1-5-2023 03:02	1-5-2023 04:26	1:24
11	A	1-5-2023 03:42	1-5-2023 04:25	0:43
12	A	1-5-2023 05:34	1-5-2023 08:53	3:19
12	A	1-5-2023 08:53	1-5-2023 08:54	0:01
13	A	1-5-2023 08:49	1-5-2023 10:43	1:54
14	A	1-5-2023 09:19	1-5-2023 12:12	2:53
14	A	1-5-2023 12:12	1-5-2023 12:14	0:02
15	A	1-5-2023 11:53	1-5-2023 14:44	2:51
15	A	1-5-2023 14:44	1-5-2023 15:05	0:21
15	A	1-5-2023 15:05	1-5-2023 16:22	1:17
16	A	1-5-2023 09:54	1-5-2023 09:59	0:05
16	A	1-5-2023 09:59	1-5-2023 10:15	0:16
16	A	1-5-2023 10:15	1-5-2023 10:21	0:06
16	A	1-5-2023 10:21	1-5-2023 12:15	1:54
16	A	1-5-2023 12:15	1-5-2023 14:45	2:30

[bdeleur]

Hi @v-binbinyu-msft ,

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.

[v-binbinyu-msft]

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.