Finding the earliest date across multiple rows

[awff]

Hi fellow PBI'ers,

I'm trying to create a measure that lists the earliest date a product was held by customers. 

Below is a simplified mock up data with the earliest dates bolded:

Client	Product	Start Date	End Date
A	1	1-Jan-21	1-Jan-22
A	1	1-Jun-10	1-May-10
B	2	1-Dec-21	1-Dec-22
B	3	1-Apr-21	1-Apr-22
B	2	1-May-19	1-May-20
C	3	1-Feb-21	1-Feb-22
C	3	1-Mar-21	1-Mar-22

The result ideally should list the earliest date possible per customer by product:

Client	Product	Earliest Date
A	1	1-Jun-10
B	2	1-May-19
B	3	1-Apr-21
C	3	1-Feb-21

I'm assuming it will be a combination of SUMMARIZE and ALL however, for some reason my results are either:
* Earliest date of the most recent row or;
* Earliest date for each product but across ALL clients rather than per client and product.

[awff]

I played around with the syntax and this seems to work as a measure:

First Start Date = 
Calculate(
    FIRSTDATE(table[Start Date]),
    Filter(
        ALLEXCEPT(table, table[Account], table[Product]),
        table[Start Date] <= Min(table[Start Date])))

 Cross checking with a small sample of the data seemed to return the correct values so far.

[awff]

Ok, So this only seems to work when drilled down clicking on a row in the table.

But when not filtered, It seems to returning the earliest date of the entire data set for the particular product!

[V-lianl-msft · Accepted solution]

Hi @awff ,

Try this measure to see if this will meet your needs:

Measure = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Client],'Table'[Product]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SteveHailey]

Hey @awff. This should do the trick:

New Table = 
ADDCOLUMNS(
    SUMMARIZE( 'Table', 'Table'[Client], 'Table'[Product] ),
    "Earliest Date", CALCULATE( MIN( 'Table'[Start Date] ) )
)

[awff]

Thanks @SteveHailey ,

I'm assuming this is for a new table

is there a way i can have this as a measure for a table visualisation?