Getting a value from unrelated table based on a date span

[Anonymous]

I've got a table containing a creation date. From an unrelated table I want to get the value from the Release column when the creation date is between the Start date and the Releasedate.

Releaseversion

Release	Releasedate	Start
21:2	2021-12-06	2021-04-01
22:1	2022-02-22	2021-12-07
22:2	2022-05-10	2021-02-23

Values

Skapad	ID	Status
2021-12-01 07:01:00	1	Avslutad
2022-02-20 16:15:00	2	Avslutad
2022-02-23 09:30:00	3	Pågående

So that I end up with

Skapad	ID	Status	Release
2021-12-01 07:01:00	1	Avslutad	21:1
2022-02-20 16:15:00	2	Avslutad	22:1
2022-02-23 09:30:00	3	Pågående	22:2

I tried added a custom column in the Power Query Editor but at the moment I just keep getting the error message "cannot convert a value of type Table to type List"

Table.ToColumns(Table.First(Table.Column(Table.SelectRows(
Table.FromRecords(Table.SelectRows(
#"Releasedatum",
each [Releasedatum] <= [Skapad] &&
), each [Start] >= [Skapad])),[Release])))

Any help would be much appreciated!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Release =
CALCULATE(MIN('Releaseversion'[Release]),FILTER(ALL('Releaseversion'),'Values'[Skapad]>='Releaseversion'[Start]&&'Values'[Skapad]<='Releaseversion'[Releasedate]))

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

[Anonymous]

Yes, thank you very much, that worked for me!

[johnt75]

You could add a calculated column in DAX, such as

Release Version =
var currentDate = Values[Skapad]
SELECTCOLUMNS(
   TOPN( 1,
      FILTER( ReleaseVersion, ReleaseVersion[Start] <= currentDate && ReleaseVersion[ReleaseDate] >= currentDate ),
      ReleaseVersion[ReleaseDate]
   ),
   "Release", ReleaseVersion[Release]
)

You may want to change the <= and >= to < and > depending on your business logic

[Anonymous]

Thanks for replying, I couldn't get that solution to work but it worked out according to the solution above!