topic Re: Return the value based on earliest date creation and by group in DAX Commands and Tips

Return the value based on earliest date creation and by group

Rondel — Thu, 18 Apr 2024 20:55:00 GMT

Hello PBI Community,

I have a table with 3 columns (Group, Value Entered, Created On). I want to create a 4th column (Original date) based on the earliest date creation by group.

Group	Value Entered	Created On	Original Date (*Desired Column)
x	1/1/2024	4/11/2024	1/1/2024
x	3/4/2024	4/12/2024	1/1/2024
x	2/2/2024	5/13/2024	1/1/2024
y	3/2/2024	1/1/2024	3/2/2024
y	1/24/2024	1/2/2024	3/2/2024

Re: Return the value based on earliest date creation and by group

lbendlin — Thu, 18 Apr 2024 22:09:45 GMT

Your sample data is inconsistent with your request. Which column is "earliest date creation" based on? Why not 1/24/2024 for group y ?

Re: Return the value based on earliest date creation and by group

Rondel — Thu, 18 Apr 2024 22:16:49 GMT

Hi lbendlin

the column [Created On] would be the system generated date.

1/24/2024 would not be for group y since it was entered on 1/2/2024 (oldest date for group y is 1/1/2024).

Hope this helps.

Re: Return the value based on earliest date creation and by group

lbendlin — Thu, 18 Apr 2024 22:35:12 GMT

Please explain how you arrive at 3/2/2024 for group y.

Re: Return the value based on earliest date creation and by group

Rondel — Thu, 18 Apr 2024 22:41:55 GMT

Sure thing -

3/2/2024 is the date value entered by an operator. This same value was created (system generated) on 1/1/2024.

3/2/2024 is the [Original Date] because the following day (1/2/2024), they changed the date from 3/2/2024 to 1/24/2024.

Re: Return the value based on earliest date creation and by group

lbendlin — Thu, 18 Apr 2024 22:57:24 GMT

ah, got it.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTLUN9Q3MjAyATJN9A2h7FgdiKSxvgmSpBGqJJALkzTVNzRGSFaCdcIl4TbA5IBcE4QkzNRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, #"Value Entered" = _t, #"Created On" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value Entered", type date}, {"Created On", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Rows", each _, type table [Group=nullable text, Value Entered=nullable date, Created On=nullable date]}, {"Min Created", each List.Min([Created On]), type nullable date}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Value Entered", "Created On"}, {"Value Entered", "Created On"}), #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Original Date", (k)=> Table.SelectRows(#"Expanded Rows", each [Group]=k[Group] and [Created On]=k[Min Created]){0}[Value Entered], type date), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Group", "Value Entered", "Created On", "Original Date"}) in #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Re: Return the value based on earliest date creation and by group

Rondel — Thu, 18 Apr 2024 23:07:00 GMT

Thank you for the quick replies! - ill execute this once I get back to my desk.

Will keep you posted!

Re: Return the value based on earliest date creation and by group

Rondel — Fri, 19 Apr 2024 03:34:48 GMT

lbendlin

Would you mind explaining what this part of the code does? I can't seem to make it work on my end:

Table.AddColumn(#"Expanded Rows", "Original Date", (k)=> Table.SelectRows(#"Expanded Rows", each [Group]=k[Group] and [Created On]=k[Min Created]){0}[Value Entered], type date)

Re: Return the value based on earliest date creation and by group

Anonymous — Fri, 19 Apr 2024 09:06:59 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi Rondel ,

Here are the steps you can follow：

1. Create calculated column.

Original Date = var _mindate= MINX( FILTER(ALL('Table'), 'Table'[Group]=EARLIER('Table'[Group])),[Created On]) return MINX( FILTER(ALL('Table'), 'Table'[Created On]=_mindate),'Table'[Value Entered])

2. Result:

Best Regards,

Liu Yang

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

Re: Return the value based on earliest date creation and by group

lbendlin — Fri, 19 Apr 2024 11:24:31 GMT

You wanted the first listed date in the group. The code filters the previous result to return all rows in the same group and then grabs the column value from the first row.

Re: Return the value based on earliest date creation and by group

Rondel — Fri, 19 Apr 2024 21:40:42 GMT

Anonymous lbendlin - I want to sincerely thank you both for your help and support!

I learned a ton from both of your recommendations and was able to implement it to our business problem!