DAX JOIN if a value is between two dates

Question

In Table A I have a date field called Sales Date. In Table B I have start date, end date, and fiscal quarter. I would like a new Table with the Sales Date from Table A and the fiscal quarter from Table B. How would I do that in DAX? Since most of the UI in PowerBI only allows equality.

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-28T16:43:45

If you want it as a new table you can do something like this:

NewTable = SUMMARIZECOLUMNS(
               TableA[SalesDate],
               "FiscalQuarter",
               CALCULATE(
                   MAX(TableB[FiscalQuarter]),
                   TableB[StartDate] <= VALUES(TableA[SalesDate]),
                   TableB[EndDate] >= VALUES(TableA[SalesDate])))

You could also just add FiscalQuarter as a calculated column on TableA:

FiscalQuarter = CALCULATE(
                    MAX(TableB[FiscalQuarter]),
                    FILTER(TableB,
                        TableB[StartDate] <= TableA[SalesDate] &&
                        TableB[EndDate] >= TableA[SalesDate]))

DAX JOIN if a value is between two dates

1 Answers