Min from related table and then between two columns

[sovereignauto]

Morning, 

i have two tables, Table2 has multiple records to Table1

the tables are linked by Ref - so im looking to get the earliest date from table2[date_1] for each row in table1

then i need to get the earliest from table1[date] and table1[the new col]

Table 2:

Ref	date deliverd
123456	01/01/2021
123789	07/01/2021
123456	04/01/2021
123789	03/01/2021

Table 1:

ref	date closed	first date deliverd	date actioned
123456		Returned from above table	=first date deliverd or date closed (depending on whats filled out)
123458	01/01/2021
123789		Returned from above table
123587	01/01/2021

hope this makes sense and thank you! 

[v-robertq-msft · Accepted solution]

Hi, @sovereignauto 

According to your description and sample data, I can clearly understand your requirement, I think you can achieve this using DAX to create two columns, you can follow my steps:

Create two calculated columns like this:

first date delivered =

MINX(FILTER(ALL('Table 2'),[ref]=EARLIER([ref])),[date deliverd])

date actioned =

SWITCH(

    TRUE(),

    [date closed]=BLANK(),[first date delivered],

    [first date delivered]=BLANK(),[date closed],

    IF([date closed]<=[first date delivered],[date closed],[first date delivered]))

And you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[webportal]

Not very sure I understood your question.

If you need a calculated column on Table2 to get the earliest date from a related Table1, you could try:

Column = MINX(RELATEDTABLE(Table2), 'Table2'[Date])

[sovereignauto]

I have updated my post with a couple of tables

so i need the earliest date from the records with the same ref but then i also need to add a column to get "actioned date" from the new column and an exsisting column so think i need it in power query rather than DAX?