topic Re: Return the earliest date among a group using DAX in DAX Commands and Tips

Return the earliest date among a group using DAX

RRamirez01 — Wed, 14 Sep 2022 18:46:50 GMT

Hello Power BI Community,

I'm currently working on some DAX expressions to reduce the amount of rows in a data extract.

From the raw data, I get multiple records that use the same columns except from "Changed status date" column. So I created a concatenation to identify which are the group items that I need a unique value from (taking the earliest date) as follows.

I want to keep only only row from each group.

Name	Invoice #	Invoice Status	Changed status Date	Concatenation
Customer A	1111	Paid	9/14/2022 4:23	Customer A1111Paid
Customer A	1111	Paid	7/26/2022 18:03	Customer A1111Paid
Customer A	1111	Paid	7/26/2022 18:03	Customer A1111Paid
Customer B	2222	Paid	9/14/2022 4:23	Customer B2222Paid
Customer B	2222	Paid	7/26/2022 18:03	Customer B2222Paid
Customer B	2222	Paid	7/26/2022 18:03	Customer B2222Paid

I've seen in other posts that "VAR" was used but I can't create the VAR expression either by creating a new column in Field or Add column in Power Query Editor.

This is my first post, so I apologize if this inquiry has been answered in this Community.

Thanks in advance!

Re: Return the earliest date among a group using DAX

Syk — Wed, 14 Sep 2022 18:54:49 GMT

In Power Query select your time and you can use the date/time filters to select the earliest record

Based on your sample data, you will still have 2 records per invoice as they are exactly the same. You can select the little table in the top left of your data and click remove duplicates.

This will give you 1 row per invoice at the earliest status change date!

Re: Return the earliest date among a group using DAX

RRamirez01 — Wed, 14 Sep 2022 19:23:52 GMT

Hi Syk, thanks for your promptly answer. May be I should have claryfied this better, In my brief example, we only have the same date for an earliest result. But in the raw data I have several different dates that in each group (Given by the concatenation) so that means that I have one record only with the earliest date by each separate group.

To keep this as simple as possible, I changed only the "Customer B2222" date

Name	Invoice #	Invoice Status	Changed status Date	Concatenation
Customer A	1111	Paid	9/14/2022 4:23	Customer A1111Paid
Customer A	1111	Paid	7/26/2022 18:03	Customer A1111Paid
Customer A	1111	Paid	7/26/2022 18:03	Customer A1111Paid
Customer B	2222	Paid	8/14/2022 4:23	Customer B2222Paid
Customer B	2222	Paid	7/26/2022 18:03	Customer B2222Paid
Customer B	2222	Paid	7/26/2022 18:03	Customer B2222Paid

I think this changes the resolution I might take here.

Re: Return the earliest date among a group using DAX

Anonymous — Thu, 15 Sep 2022 09:40:50 GMT

Hi RRamirez01 ,

I think you can try earliest function in table value field.

Best Regards,
Rico Zhou

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