Finding MIN date for multiple related records

[Anonymous]

Hi,

I'm working with one table where I'd like to write a measure that identifies the earliest date for any given set of records.  In the screenshot below, I've highlighted the earliest date for each set of records.

Thanks,

J

[v-sihou-msft · Accepted solution]

@Anonymous

For your requirement, you can use ALLEXCEPT() to get the MIN date group on each id. 

Measure = CALCUATE(MIN(Table[CreateDate]),ALLEXCEPT(Table,Table[OpportunityId]))

Regards,

[TomMartens]

Hey,

I'm not sure if a calculated column would also suit your case, if this could be, than you might have a look at this pbix file

On the report page "First Date In Group" there is a table viz, that uses the table "FactWithDates" from my model, this table has two calculated columns

"First Date by Customer bool col" and "First Date by Customer col".

The first calculated column returns 1 if the date in the current row is the first date of the customer, where as the 2nd calculation returns the first date of the customer.

I tend to implement this kind of requirements as calculated columns due to the possibility to use these columns as slicer and as axis on slicer.

If you have to implement your requirement as measure, can you please add, why a measure is necessary and a calculated column is not enough.

Regards

[Anonymous]

@TomMartensThank you for the file...  It certainly put me on the right track.  Ultimately, I've solved using a calculated column approach, but it does not sit well with me because it dedicates too many resources memorywise.

I suspect I need to learn about variables to get it done within a measure.

The calculated column leveraged the ALLEXCEPT( ) function.

Thanks for your help!