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

pva great solution from bcdobbs but you can also try the

Filter = 
VAR __minDate = MIN ( 'Calendar'[Date] )
VAR __maxDate = MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
    COUNTROWS ( 'Table' ),
    ( 'Table'[Start date] >= __minDate && 'Table'[Start date] <= __maxDate ) ||
    ( 'Table'[End date] >= __minDate && 'Table'[End date] <= __maxDate )
)

 

 

✨ Follow us on LinkedIn and  to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

following measure and then use this measure as visual level filter where value >= 1

 

 

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Nice solution parry2k !

    I think yours is potentially faster! Tempted to find/generate a large dataset and test.

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      I suspect totally academic (but felt like nerding it up) unless the dataset is massive but with a random 10,000,000 row table:

      My original DAX ran in 9674ms, parry2k ran in 10267 ms which I'd count as virtually identical in terms of user experience.

       

      Best I've been able to achieve is the following modification to my code which trims it down to 8491 ms.

       

      Show Row = 

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

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

VAR AllDates = DISTINCT ( UNION ( StartDates, EndDates ) )

VAR Result = 
    CALCULATE (
        INT ( NOT ( ISEMPTY ( 'Calendar' ) ) ),
        KEEPFILTERS( AllDates ) 
    )

RETURN Result