Group items by date using a set of start and end dates

[riegersn]

So I have 2 tables. The data table has a resolvedDate (datetime). The other table, which I guess would be a dimension table, has an index column, start date and end date both in datetime. Id like to add a column on the data table that references the index to the dimension table using the resolvedDate that falls between the given start and end dates. I understand this may be simpler with ust dates and not datetimes but in this instance I need to group using the datetimes. Whats the best approach here?

[v-henryk-mstf]

Hi @riegersn ,

Don't seem to quite understand your requirements, there doesn't seem to be a related field between the two tables. Make sure this is the correct data model?

Can you provide relevant test data and screenshots of the desired results. Easy for me to answer for you as soon as possible. Looking forward to your feedback.

Best Regards,
Henry

[TheoC]

Hi @riegersn 

Can you try the following:

Column = 

CALCULATE ( 
     VALUES ( Table2[Index] ) , 
     FILTER ( Table2 , Table2[Start Date] <= EARLIER ( Table1[Resolved] ) && Table2[End Date] >= EARLIER ( Table1[Resolved] ) ) )

Please make sure to adjust the names of your tables accordingly. 

Hope this helps.

Theo

[TheoC · Accepted solution]

Hi @riegersn 

Can you try the following:

Column = 

CALCULATE ( 
     VALUES ( Table2[Index] ) , 
     FILTER ( Table2 , Table2[Start Date] <= EARLIER ( Table1[Resolved] ) && Table2[End Date] >= EARLIER ( Table1[Resolved] ) ) )

Please make sure to adjust the names of your tables accordingly. 

Hope this helps.

Theo