get value from another table by expression

[emilypinhasi]

Hi

i have a table Sprints like:

id  | start date | end date

i have another table Issues that has a column that contains date, lets call it changeDate

i would like to add to that table a column that returns the id from the first table if changeDate is between start and end dates

tried something like:

FIRSTNONBLANK(Sprints[id],DATESBETWEEN(Issues[changeDate], Sprints[startDate].[Date], Sprints[endDate].[Date]))

but i get errors "A single value for column 'startDate' in table 'Sprints' cannot be determined. "

any idea how to acomplish this?

[TomMartens]

hey,

My both tables look like this

Table1

aDate
2017-01-23
2017-01-15
2017-01-26
2017-02-15

Table2 looks like this

id	startdate	enddate
1	2017-01-01	2017-01-10
2	2017-01-11	2017-01-21
3	2017-01-22	2017-02-10
4	2017-02-11	2017-02-28

I create a calculated column in Table1 that pulls the id from Table2 from that record where the column aDate is between startdate and enddate (assumption, there is no overlap in the ranges and there are also no gaps in the ranges).

The calculated column

CALCULATE(
    MAX('Table2'[id])
    ,FILTER(
        'Table2','Table2'[startdate] <= 'Table1'[aDate] && 'Table2'[enddate] >= 'Table1'[aDate])
)

Hope this helps

Regards

[emilypinhasi]

thanks, this is whay i needed

but, i do have gaps in the sprint dates, and i assume this is why im getting the same sprint for all rows

[TomMartens]

Hey,

can you please share some sample data.

Regards

[MarcelBeug]

You are refering to the DAX solution?

The Power Query solution should work fine.

[MarcelBeug]

My suggestion would be to expand the Sprints table, so you have a row for each date.

This is done by importing the dates as whole number and later on these are converted back to dates.

Now you can merge the other table with the expanded sprints table on the date columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Sprints"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"start date", Int64.Type}, {"end date", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {[start date]..[end date]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"start date", "end date"}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Removed Columns", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}})
in
    #"Changed Type1"

let
    Source = Table.NestedJoin(AnotherTable,{"changeDate"},ExpandedSprints,{"Dates"},"ExpandedSprints",JoinKind.LeftOuter),
    #"Expanded ExpandedSprints" = Table.ExpandTableColumn(Source, "ExpandedSprints", {"id"})
in
    #"Expanded ExpandedSprints"