Forum Discussion
Solved
lead time and filters
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/...
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,
KellyDid I answer your question? Mark my reply as a solution!
v-kelly-msft
Community Support
Community SupportHi 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!
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.