Forum Discussion
Getting a value from unrelated table based on a date span
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!
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
4 Replies
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
johnt75Super User
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