PowerBI merges or lookups of dates to date ranges (without a shared key variable)

user_18346528 0
Viewed 28

There are several, related, answers to the question of fuzzy joins in PowerBI - allowing for dates within ranges to be assessed. See: power query merge two tables based on the transaction date between two dates for example.

My challenge is that I have a list of date ranges, and a list of dates, but no shared key variable, e.g. "EmpID" in the above example, so my datasets look like:

Dates
2020-01-01
2021-01-01
2022-01-01

and my date ranges are like these:

Date_Start  Date_End   Clasifier
2019-01-01  2019-01-02 A
2019-12-31  2020-01-02 B
2020-01-01  2020-01-02 A
2020-01-01  2024-01-01 C

Is there a way to ask if a given Date (first table) is within at least one of these ranges (second table) in DAX or in M? If there is a method, can it return the date, perhaps the over-lapping date range, and the relevant classifier associated with the date range?

dax powerbi fuzzy-comparison date
1 Answers

Create a calculated column in the front end inside of the Dates only table lets say dates only table is Table1 and Classifier table as Table2

   Column =
    IF (
        MAX ( 'TABLE2'[Date_Start] ) <= MAX ( 'TABLE1'[Dates] )
            && MAX ( 'TABLE2'[Date_End] ) > MAX ( 'TABLE1'[Dates] ),
        MAX ( 'TABLE2'[Clasifier] ),
        'N/A'
    )
user_105834340
Related