A measure that can be filtered by 2 date columns using 1 slicer

[Orman01]

Hi Everyone! I have this requirement to create a slicer where a measure should be filtered by both start date and end date. (ex. We need to see the total tickets created and completed from Jan 1 to Jan 31 (Between Date Slicer)). Tried using USERELATIONSHIP but it can only filter one date column.

CREATED_COMPLETED = CALCULATE(DISTINCTCOUNT('RAW'[TICKETNUMBER]),USERELATIONSHIP('RAW'[DATE CREATED],'Date Table'[Date]),USERELATIONSHIP('RAW'[DATE COMPLETED],'Date Table'[Date]))

Thank you.

[Orman01]

This works! Thanks a lot! 

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

created and completed: = 
VAR _t =
    FILTER (
        RAW,
        RAW[DATE COMPLETED] >= MIN ( 'Date Table'[Date] )
            && RAW[DATE CREATED] <= MAX ( 'Date Table'[Date] )
    )
RETURN
    COUNTROWS ( _t )

[Orman01]

Thank you. I appreciate your response, however, I'm not sure why but some filters are not showing accurate numbers.

[MasonMA · Accepted solution]

@Orman01 

Hi, in this case you would need to disconnect your Date table from RAW table and create one measure for filtering your report. 

You can refer to my testFile. I used this demo measure below and you can adjust it based on your real situation.

Tickets_CreatedAndCompleted = 
CALCULATE(
    DISTINCTCOUNT('Tickets'[TICKETNUMBER]),
    FILTER(
        'Tickets',
        'Tickets'[DATE CREATED] >= MIN('Date Table'[Date]) &&
        'Tickets'[DATE CREATED] <= MAX('Date Table'[Date]) &&
        'Tickets'[DATE COMPLETED] >= MIN('Date Table'[Date]) &&
        'Tickets'[DATE COMPLETED] <= MAX('Date Table'[Date])
    )
)

 Hope it helps:)