Hw do I add a column to a table where a date is between two dates in a second table?

[REngels]

I am trying to add a column to table (w). Each row of table (w) is an activity that occurred on a date.  The new value I want to add is the name of a date range stored in a different table (s). This SQL statment would get me what I want, but I'm struggling to write it in DAX. Suggestions?

Select s. sprintname, w.key, w.loggedtime, w.user

From sprints s, worklogs w

Where w.key = s.key

And wl.timeentrydate between s.sprintstartdate and s.sprintenddate;

I don't necessarily need to add the value to (w). Any solution that will allow me to associate the date with the date range will be great.

Thanks.

[v-rzhou-msft · Accepted solution]

Hi @REngels ,

I think you can try this code to create a caluclated column in table w.

sprintname =
CALCULATE (
    MAX ( S[sprintname] ),
    FILTER (
        S,
        S[Key] = EARLIER ( W[Key] )
            && S[sprintstartdate] <= EARLIER ( W[timeentrydate] )
            && S[sprintenddate] >= EARLIER ( W[timeentrydate] )
    )
)

My Sample:

S

W

Result is as below

Best Regards,
Rico Zhou

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