JOIN Table A Value A to Table B where A is between two values

[PBI_Inquisitor]

Hi

Table A contains a set of results by employee by week. 

Employee	WeekKey	Result
12345	20250526	16
12345	20250602	20
12345	20250609	18

Table B contains a target by employee with a week date range

Employee	FromWeek	ToWeek	Target
12345	20250505	20250526	21
12345	20250602	20250630	20

What I want to do is to join these on Employee AND where A.WeekKey BETWEEN B.FromWeek and B.ToWeek

Result would be:

Employee	WeekKey	Result	Target
12345	20250526	16	21
12345	20250602	20	20
12345	20250609	18	20

Any ideas and suggestions really appreciated.

[PBI_Inquisitor]

Thank you both. I had some success with both options but ultimately couldn't deliver what I needed in the time allowed so stripped it back and did most of the heavy work in Oracle SQL

I'll revert back here later to retry as think this should and will work and will help my learning.

Many thanks to you for taking the time to help. 

Phil

[v-achippa]

Hi @PBI_Inquisitor,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Excel, @samratpbi and @SamsonTruong for the prompt response.

Thank you for the response, As we haven’t heard back from you, we wanted to kindly follow up to check if the issue is resolved? or let us know if you need any further assistance.
If any response helped in resolving the issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @PBI_Inquisitor,

As we haven’t heard back from you, we wanted to kindly follow up to check if the issue is resolved.
If any response helped in resolving the issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Ashish_Excel]

Hi,

This calculated column formula in Table1 works

=CALCULATE(SUM(Table2[Target]),FILTER(Table2,Table2[Employee]=EARLIER(Table1[Employee])&&Table2[FromWeek]<=EARLIER(Table1[WeekKey])&&Table2[ToWeek]>=EARLIER(Table1[WeekKey])))

Hope this helps.

[samratpbi]

Hi, there are many ways you may able to achieve this. One is to use LOOPUPVALUE and bring the value of one table into another. However the best would be to join the tables in Power Query. You can merge 2 tables based on multiple columns. 

I created 2 tables:

T1:

T2:

Then merge T2 into T1 with Employee column. Then add a conditional column:

if [WeekKey] >= [T2.FromWeek] and [WeekKey] <= [T2.ToWeek] then 1 else 0

then filter out only values with 1.

Below is the Power Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEyMjAyNTA1MgMyDc2UYnXQZMwMjMBMbDKWID0WSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, WeekKey = _t, Result = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", Int64.Type}, {"WeekKey", Int64.Type}, {"Result", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Employee"}, T2, {"Employee"}, "T2", JoinKind.Inner),
#"Expanded T2" = Table.ExpandTableColumn(#"Merged Queries", "T2", {"FromWeek", "ToWeek", "Target"}, {"T2.FromWeek", "T2.ToWeek", "T2.Target"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded T2", "Custom", each if [WeekKey] >= [T2.FromWeek] and [WeekKey] <= [T2.ToWeek] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"T2.FromWeek", "T2.ToWeek", "Custom"})
in
#"Removed Columns"

Output is:

If this helps to resolve your problem, then please mark it as solution. Dont forget to give kudos! - Thanks

[SamsonTruong · Accepted solution]

Hi @PBI_Inquisitor , are you trying to achieve this in PowerQuery or as a Calculated Table?

Assuming you are attempting to do this in a DAX Calculated Table, please try the following DAX:

JoinedTable =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN ( TableA, TableB ),
        TableA[Employee] = TableB[Employee]
            && TableA[WeekKey] >= TableB[FromWeek]
            && TableA[WeekKey] <= TableB[ToWeek]
    ),
    "Employee", TableA[Employee],
    "WeekKey", TableA[WeekKey],
    "Result", TableA[Result],
    "Target", TableB[Target]
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session