MIN date using M

[Arnault_]

Dear All,

My business case is the following :

I have orders identified by a specific order_id and each order can be received in several sequences (reception_id) with different reception dates. I want to identify whether or not for each order_id, this is the first reception (earliest date).

In DAX, I have created the following formula which works perfectly and I would like to translate it into M language.

first_reception = IF('TABLE'[reception_date]<=CALCULATE(MIN('TABLE'[reception_date]);ALLEXCEPT('TABLE';'TABLE'[order_id]));"YES";"NO")

The dataset  :

order_id	reception_id	reception_date
O001	R001	01/01/2019
O001	R002	01/01/2019
O001	R003	15/02/2019
O002	R004	12/02/2019
O002	R005	13/02/2019
O002	R006	14/02/2019
O002	R007	15/02/2019
O003	R008	16/02/2019
O004	R009	17/02/2019
O005	R010	18/02/2019
O006	R011	19/02/2019

[Arnault_]

Thanks both of you for the proposed solution. It works perfectly. Cheers

[Anonymous · Accepted solution]

Hi,

I have pasted your data into an excel sheet and loaded it into power bi and edited the Power Query to identify the first reception date. Given below is the Power Query.

let
    Source = Excel.Workbook(File.Contents("C:\Users\R\Orders.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"order_id", type text}, {"reception_id", type text}, {"reception_date", type date}}),

//added codes starts here..
    TempTable = #"Changed Type",
    Orders = #"Changed Type",
//this function X filters the table based on the parameter "order_id"

    X = (y as table,z as text ) as table => Table.SelectRows(y,each Record.Field(_,"order_id")=z),

//Adds the column 'first_reception' to the table.

    AddColumn = Table.AddColumn(
        Orders,
        "first_reception",
        each Record.Field(_,"reception_date") = List.Min(  // comparison of current row's reception date with the minimum of all receptions dates of that order.
            Table.Column( //Table.Column converts the column to a list which is passed on to List.Min()
                X(TempTable,Record.Field(_,"order_id")),"reception_date" // filters the table bsed on current record's order_id and passing it on to Table.Column() funciton
            )
        )
    )
in
    AddColumn

[Zubair_Muhammad]

@Arnault_ 

You can use this Custom Column

let myorderid=[order_id]
in
List.Min(Table.SelectRows(#"Previous Step Name",each [order_id]=myorderid)[reception_date])=[reception_date]