CODEWITHSUNDEEP
powerbidax
Anthony
Anthony

Reputation: 923

DAX JOIN if a value is between two dates

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.

Upvotes: 1

Views: 5768

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

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]))

Upvotes: 4

Related Questions