lead time and filters

[pintoan]

I'm new to power bi. I' m trying to calculate the difference between two dates. Here is a example of my data.

id  operation   date    team
333 A   13/08/21    A
454 C   13/08/21    A
433 F   15/08/21    A
333 C   14/08/21    A
333 D   15/08/21    A
454 A   12/08/21    A
766 A   17/08/21    B
766 D   19/08/21    B
454 B   15/08/21    A
333 B   13/08/21    A

I want to calculate the average time between operation from all ids from one team. I need to be able to filter this by operations and team. example: average time between op A&C from team A. Any help will be very appreciated.

[amitchandak]

@pintoan , You can get a column, which can give you diff

time between Ops =
var _max = maxx(filter(Table, [Team] = earlier([team]) && [Date] < ealier([Date])), [Date])
return
datediff(_max,[date], day)

you can create measure based on this diff

[pintoan]

I think i asked the wrong question. This is just a example. If i search for the difference between operation L and A the result is 2. But with the solution you gave i dont have this.

For example OP C- OP B should be 0 not 1

id	Operation	TEAM	date
500	A	A	03/08/2021
500	B	A	04/08/2021
500	C	A	04/08/2021
500	D	A	04/08/2021
500	E	A	04/08/2021
500	F	A	04/08/2021
500	G	A	04/08/2021
500	H	A	04/08/2021
500	I	A	04/08/2021
500	J	A	04/08/2021
500	L	A	04/08/2021
500	M	A	04/08/2021
500	N	A	05/08/2021
500	O	A	04/08/2021
500	P	A	05/08/2021

[v-kelly-msft · Accepted solution]

Hi  @pintoan ,

Based on my understanding,date for L is 04/08/2021,date for A is 03/08/2021,so the result should be 1.

If so,check below measure：

Measure =
VAR _opermax =
    CALCULATE ( MAX ( 'Table'[Operation] ), ALLSELECTED ( 'Table'[Operation] ) )
VAR _opermin =
    CALCULATE ( MIN ( 'Table'[Operation] ), ALLSELECTED ( 'Table'[Operation] ) )
VAR _team =
    SELECTEDVALUE ( 'Table'[TEAM] )
VAR _datemax =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[TEAM] = _team
                && 'Table'[Operation] = _opermax
        )
    )
VAR _datemin =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[TEAM] = _team
                && 'Table'[Operation] = _opermin
        )
    )
RETURN
    DATEDIFF ( _datemin, _datemax, DAY )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[pintoan]

I have another question, this works fine when selecting 1 id, but i have multiple ID and want a average of time of them. Many thanks for this anyway, is a great upgrade for what i had.