Forum Discussion

pva's avatar
pva
Frequent Visitor
4 years ago
Solved

Date filter

Hi all! I have a table with different sprints with their own star and end date. I want to filter the table like this: if I choose december 2021 I wanto it to show all the sprints that started or fini...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Hi Pedro have a look at this demo.

     

    I used a disconnected date table.

     

    Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE (2020, 1, 1), DATE (2022, 12, 31) ),
    "Month Year", DATE ( YEAR([Date]), MONTH([Date]),1) //Format as MMMM YYYY
)

    Set both of them as Date type and then set format of Month Year to MMMM YYYY

     

    Created a measure in the Sprint table:

    Show Row = 

VAR DatesInContext = 
    VALUES ( 'Calendar'[Date] )

VAR StartDates = 
    TREATAS ( 
        VALUES ( Sprint[Start date] ),
        'Calendar'[Date] 
    )

VAR EndDates = 
    TREATAS ( 
        VALUES ( Sprint[End Date] ), 
        'Calendar'[Date] 
    )

VAR AllDates = 
    UNION (StartDates, EndDates)

VAR CountDateIntersect = 
    COUNTROWS(
        INTERSECT ( DatesInContext, AllDates )
    )

VAR Result = 
    IF ( CountDateIntersect > 0, 1, 0 )

RETURN Result

     

    Created a table visual and added the measure as a visual level filter:

     

     

parry2k's avatar
parry2k
Super User
4 years ago

bcdobbs Awesome for doing the stress test, if you can share the large dataset you tested against, I have further ideas to improve the performance. Good one!

smpa01's avatar
smpa01
Community Champion
4 years ago

pva  try using a measure like this. bcdobbs  used your pbix

 

Measure = 
VAR _yr =
    VALUES ( _calendar[Year] )
VAR _mo =
    VALUES ( _calendar[Month] )
VAR _ret =
    CALCULATE (
        MAX ( Sprint[Sprint] ),
        FILTER (
            Sprint,
            (
                YEAR ( Sprint[Start date] )
                    IN _yr
                        || YEAR ( ( Sprint[End date] ) IN _yr )
            )
                && (
                    MONTH ( Sprint[Start date] )
                        IN _mo
                            || MONTH ( Sprint[End Date] ) IN _mo
                )
        )
    )
RETURN
    _ret