Need first date from first schedule line in column to be able to create right table. not sure how.

[AlexJB]

Hi,

So below u see some demo data that is needed for this question.

I already made a new column Ordernr + itemid to create a unique key so this is also able to be used. what I want to achieve is in the last column. I want for each order+itemid the first scheduled conf date. so where schedule line is 1 I want that date for each schedule line. 

It is not the first date because it is possible that the second schedule line is earlier then the first.

What I want is to use this date as the first order date for this product.  For some reason it is difficult to get this in a different way. In the end I need to create a date table from these dates and then connect it to this first order date. to be real honest I need to get these schedules line out of the file. but there is alot of other data on different schedule lines which I need. 

I hope I am clear.  thanks in advance

Ordernr	itemid	schedule line	conf date	(new column created) = ordernr +temid	What I want to get: first order date
10010	10	1	10-01-2023	1001010	10-01-2023
10010	10	2	12-01-2023	1001010	10-01-2023
10010	10	3	12-02-2023	1001010	10-01-2023
10010	20	1	10-01-2023	1001020	10-01-2023
10010	30	1	12-01-2023	1001030	12-01-2023
10020	10	1	14-01-2023	1002010	14-01-2023
10020	20	1	15-01-2023	1002020	15-01-2023
10020	20	2	12-01-2023	1002020	15-01-2023

[Bmejia]

Create a new column

MinDate =
CALCULATE(
     MIN('last date'[conf date]),

         ALLEXCEPT('last date','last date'[new column created) = ordernr +temid]))

[AlexJB]

I do not see how this works. For now I have

= Table.AddColumn(#"Inserted Merged Column", "First order date", each if [Schedule line] = 1 then [conf date] else null)

Although this gives for every other schedule line after one null as data. That data has to give the same data as was given in the line of schedule line 1..  So it has to look up the value of the combi order+itemid + schedule line = 1 and give that date for all the other lines..  but I cant get this to work. tried several things already.

[AlexJB · Accepted solution]

I fixed it by creating a new table with only Ordernr + temid and the newly created First order date. Then I removed all rows with null as value. 

To use it in visual I linked my date table to this one and on date level. then another relation from order+temid to order+temid relation to the first table

I got the same results as using the data without the extra table and only using the following formula. so for my report the extra step wasnt needed, I still do use it for future calculations.

= Table.AddColumn(#"Inserted Merged Column", "First order date", each if [Schedule line] = 1 then [conf date] else null)