Forum Discussion
Nested MIN/MAX
Hello
I have the table:
| Col1 | Col2 | Col3 |
| A | 1 | 01/01/2000 |
| A | 2 | 02/01/2000 |
| A | 1 | 03/01/2000 |
| A | 2 | 04/01/2000 |
| A | 1 | 05/01/2000 |
| A | 1 | 06/01/2000 |
| A | 1 | 07/01/2000 |
etc
After grouping by Col1 (which can be A, B, etc), I want to find the earliest date where Col2=1 after the latest date where Col2=2 for that group A (or B, C etc).
Any idea?
Thanks!
Are you expecting this kind of result?
You can use the measures below:
Earliest Date = CALCULATE ( MIN ( Data[Col3] ), Data[Col2] = 1, ALLEXCEPT ( Data, Data[Col1] ) )Latest Date = CALCULATE ( MAX ( Data[Col3] ), Data[Col2] = 2, ALLEXCEPT ( Data, Data[Col1] ) )
2 Replies
Hi Anonymous ,
Can you please elaborate this?
Col2=1 after the latest date where Col2=2
Are you expecting this kind of result?
You can use the measures below:
Earliest Date = CALCULATE ( MIN ( Data[Col3] ), Data[Col2] = 1, ALLEXCEPT ( Data, Data[Col1] ) )Latest Date = CALCULATE ( MAX ( Data[Col3] ), Data[Col2] = 2, ALLEXCEPT ( Data, Data[Col1] ) )