Dax and date ranges

[busmansholiday]

I have reviewed Solved: Lookup value within date range - Microsoft Power BI Community and other related posts of matching a date in one table to a date range in a second table.  However, these examples seem to assume that there is another categorical variable that I can use for the initial inner-join. My situation is different.

I have a list of dates:

2019-01-01

2020-01-01

2021-01-01

2022-01-01

and I have a list of intervals

start             stop            Categorical_Value

2019-01-01 2019-12-31 A

2019-12-31 2020-01-02 B

2019-12-31 2023-01-01 C

2020-01-02 2021-12-30 D

For each date (first list), I want to know which intervals overlap with that list, so for 2019-01-01 I would like a record of  the date and null values for start, stop and categorical_value.

Date   start             stop                     categorical value

2019-01-01 2019-12-31 2020-01-02 B

2020-01-01 2019-12-31 2023-01-01 C

Appropriate DAX for ths sort of join/lookup completely escapes me.

R

[v-easonf-msft · Accepted solution]

Hi, @busmansholiday 

Is this you expected result?

If so, you may need to join the two tables before filtering the data, because it is currently difficult to expand the rows by measure.  'lookupvalue'  also does not apply to row data expansion.

Sample:

New Table =
VAR tab =
    CROSSJOIN ( List, 'Table' )
VAR tab2 =
    FILTER ( tab, List[Date] > 'Table'[start] && List[Date] < 'Table'[stop] )
RETURN
    tab2

Best Regards,
Community Support Team _ Eason

[amitchandak]

@busmansholiday , assume the first date is an independent table

then you create a measure like this

new measure =

var _max = maxx(allselected(Date1),Date1[Date])

return

countrows(filter(Table2, Table2[Start Date] <= _max && Table2[End Date] >= _max ))