Forum Discussion
lead time and filters
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!
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 |
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.