AVERAGEX over datediff events in a single collumn

[Johannes_W]

Hi all!

I am struggeling to define a measure that calculates the average time passed between several events. I am working with averagex and datediff but somehow end up nowhere.

The data are structured as such

Product	event_id	event_start
A	1	01/01/2020
A	2	04/01/2020
A	3	06/01/2020
B	1	01/01/2020
B	2	04/01/2020
C	1	15/02/2020
C	3	17/02/2020
D	1	04/01/2020
D	2	15/02/2020
D	3	17/02/2020
D	4	16/04/2020

Now the goal is to find out the average datediff in days that passes between event ID 1 and event ID 2.

The problem is, not all products necessarily see event 2, some jump to event 3 directly.

My expected result for this measure would be 16 days.

Product A and B have seen 3 days jumping from 1 to 2, D has seen 42 days, so on average 16.

Thanks for any input!

jo

[OwenAuger · Accepted solution]

Hi Jo,

Here is how I would write such a measure

Average Days Event1-Event2 = 
AVERAGEX ( 
    VALUES ( Events[Product] ),
    VAR DateEvent1 = 
        CALCULATE ( MAX ( Events[event_start] ), Events[event_id] = 1 )
    VAR DateEvent2 = 
        CALCULATE ( MIN ( Events[event_start] ), Events[event_id] = 2 )
    RETURN
        IF (
            NOT ISBLANK ( DateEvent1 ) && NOT ISBLANK ( DateEvent2 ),
            DateEvent2 - DateEvent1
            -- Blank result returned if either Event 1 or 2 missing
            -- Blanks are ignored by AVERAGEX
        )
)

This measure iterates over the values of Product, and for each calculates the difference between Event 2 date and Event 1 date, averaging the result.

The MAX & MIN are arbitrary, as I'm assuming that event_id values are not repeated for a given Product.

Regards,

Owen

[Johannes_W]

Hi Owen!

Thanks for this, it works!

Just a few clarifications:

I have not mentioned this but yes, event_id can repeat, so a product can fall back to status 2 after having reached status 3 for example.

However, I am only interested in the extremes, so earliest date of ID 1 and latest date of ID 2, so MIN MAX as below should be ok?

MIN ( Events[event_start] ), Events[event_id] = 1  )
MAX( Events[event_start] ), Events[event_id] = 2  )

The same should easily work for MEDIANX, right? Or are there any pitfalls.

best and thanks!!

Joh

[OwenAuger]

Hi Joh,

Glad it works!

On your first question about earliest ID 1 & latest ID 2: Yes, if you want the extremes then the existing formula is fine.

On your second question: Yes you can replace AVERAGEX with MEDIANX, but MEDIANX treats BLANK values as zero, while AVERAGEX ignores them. For this reason, you would have to rewrite the measure to filter out BLANK values. For example:

Median Days Event1-Event2 =
VAR ProductDuration =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Events[Product] ),
            "@Duration",
                VAR DateEvent1 =
                    CALCULATE (
                        MAX ( Events[event_start] ),
                        Events[event_id] = 1
                    )
                VAR DateEvent2 =
                    CALCULATE (
                        MIN ( Events[event_start] ),
                        Events[event_id] = 2
                    )
                RETURN
                    IF (
                        NOT ISBLANK ( DateEvent1 )
                            && NOT ISBLANK ( DateEvent2 ),
                        DateEvent2 - DateEvent1
                    )
        ),
        NOT ISBLANK ( [@Duration] )
    )
RETURN
    MEDIANX (
        ProductDuration,
        [@Duration]
    )

Regards,

Owen

[Johannes_W]

Thanks Owen, works like a charm!

Joh