Forum Discussion

Mumin192's avatar
Mumin192
Helper I
3 years ago
Solved

Filter date range based on another date range

Hello,

 

I have a table that have date start and date end

I have a date filter

I want to filter rows that have have at least one day in the date range

Is there something wrong with the logic, here?

Measure = CALCULATE(DISTINCTCOUNT(work_sessions_processed[user_id]),or(work_sessions_processed[date_end]>=MIN('Calendar'[Date]),work_sessions_processed[date_start]<=MAX('Calendar'[Date])))
 
Thanks
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Mumin192 

    please try

    Measur =
CALCULATE (
    DISTINCTCOUNT ( work_sessions_processed[user_id] ),
    FILTER (
        work_sessions_processed,
        VAR StartDate = work_sessions_processed[date_start]
        VAR EndDate = work_sessions_processed[date_end]
        VAR Dates1 =
            CALENDAR ( StartDate, EndDate )
        VAR Dates2 =
            VALUES ( 'Calendar'[Date] )
        RETURN
            NOT ISEMPTY ( INTERSECT ( Dates1, Dates2 ) )
    )
)

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Mumin192 

    please try

    Measur =
CALCULATE (
    DISTINCTCOUNT ( work_sessions_processed[user_id] ),
    FILTER (
        work_sessions_processed,
        VAR StartDate = work_sessions_processed[date_start]
        VAR EndDate = work_sessions_processed[date_end]
        VAR Dates1 =
            CALENDAR ( StartDate, EndDate )
        VAR Dates2 =
            VALUES ( 'Calendar'[Date] )
        RETURN
            NOT ISEMPTY ( INTERSECT ( Dates1, Dates2 ) )
    )
)