How to add a between filter based on columns in other table?

[MDePauw]

Hello,

I'm trying to create a dynamic last 7 day filter in PowerBI, but after several hours I can't seem to get it working.

I'm currently thinking of the current table setup (unlinked tables in the model)

DIM_CALENDAR: Basic calendar table
DIM_CALENDAR_2: Contains 1 row having a Startdate and a EndDate. (Date will be selected in slicer so only 1 row is returned)

I want to filter the data in DIM_CALENDAR to only get the dates between the StartDate and Enddate in DIM_CALENDAR_2.

How can this be achieved in PowerBI?

I was trying to create a flag using the following DAX calculation but this doesn't seem to work.

BetweenDates = IF (
'DIM_CALENDAR'[Date] > CALCULATE ( MAX ( DIM_CALENDAR_2[StartDate] ); ALLSELECTED ( DIM_CALENDAR_2 ) )
&&
'DIM_CALENDAR'[Date] < CALCULATE ( MAX ( DIM_CALENDAR_2[EndDate] ); ALLSELECTED ( DIM_CALENDAR_2 ) )
; 1; 0 )

If I add the three individual columns (Start_Date, End_date & date) in a list the three correct values appear.

The flag however is not calculated correctly.

Can someone please assist or point me in the right direction?

Thanks in advance,

M

[v-yuezhe-msft]

@MDePauw,

Create a measure instead of column using DAX below. If it doesn't return your expected result, please share dummy data and post expected result.

BetweenDates = IF (
MIN('DIM_CALENDAR'[Date])> CALCULATE ( MAX ( DIM_CALENDAR_2[StartDate] ); ALLSELECTED ( DIM_CALENDAR_2 ) )
&&
MAX('DIM_CALENDAR'[Date]) < CALCULATE ( MAX ( DIM_CALENDAR_2[EndDate] ); ALLSELECTED ( DIM_CALENDAR_2 ) )
; 1; 0 )

Regards,
Lydia