Find first date in column based on another column - need it in M

[grggmrtn]

I've created a custom column in DAX, which finds the first [Startdate] based on a column that is the [Service ID) (unfortunatly people have been inputting several start dates, bad data discipline - I need the first one to correct that error).

First Startdate = 
CALCULATE (
    MIN ( 'MyTable'[Startdate] );
    FILTER (
        'MyTable';
        'MyTable'[Service ID]
            = EARLIER ( 'MyTable'[Service ID] )
    )
)

But several of my columns in my query code (M) are based on [Startdate], so I would LOVE it if I could do this same thing in M.

Is it possible?

[TomMartens · Accepted solution]

Hey,

it's possible in M, here you will find a little pbix file.

Basically create a Group By with two aggregations,

Use the column [Service ID] to group by

use two aggregations

• use the Min Aggregation with your column Startdate
• use the All Rows Aggregation with

Using All Rows creates a table preserving all columns and allows to expand the table in a later step. Just select all the grouped columns except ServiceID

A screenshot from the Group By dialog

After selecting OK, you can expand the table:

Selecting all the column that have been grouped:

Done 🙂

Hopefully this is what you are looking for.

Regards,

Tom

[grggmrtn]

That's beautiful @TomMartens - THANKS!!!