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 finished in this period. How can I do it?

 

This is the table:

 

SprintStart dateEnd date
12021/12/312022/01/15
22021/07/312021/12/15
32021/02/202021/04/12
42021/12/072021/12/31

 

This is the result I am looking for (filtering december 2021) The filter has to take into account both start and end date

 

SprintStart dateEnd date
12021/12/312022/01/15
22021/07/312021/12/15
42021/12/072021/12/31

 

Thanks for your support.

 

Pedro

 

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:

     

     

11 Replies

  • 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

         

         

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    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!

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      Go for it! Dataset is nothing very fancy:

       

      BigSprint = 
VAR BaseTable = GENERATESERIES( 1, 10000000, 1 )

RETURN 
    GENERATE (
        BaseTable,
        VAR BaseId = [Value]
        VAR StartDate = DATE (2020, RANDBETWEEN( 1, 12 ), RANDBETWEEN( 1, 28 ) )
        VAR EndDate = StartDate + RANDBETWEEN(2,60)
        RETURN 
        ROW (
            "SprintId", BaseId,
            "Start Date", StartDate,
            "End Date", EndDate
        )
    )

       


      File available here.

      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

        Cracked it! Good lesson in making the data model do the work rather than the DAX. Analysis service never ceases to amaze me...

         

        Took same base table with 10000000 rows and expanded it out to the day granualarity. Eg each SprintId had a row per day. Could do it in powerquery or ETL but I used generate in DAX:

        SprintExpanded = 
    GENERATE (
        BigSprint,
        DATESBETWEEN('Calendar'[Date], BigSprint[Start Date], BigSprint[End Date] )
    )

        (took my laptop a fair amount of time to crunch and resulting table could do with columns being tidied)

        You can then leverage a normal relationship:

         

        No need for a visual level filter and I'm getting an order of magnitude smaller. 695ms

         

        Sorry pva the intial code from either myself or parry2k  is absolutely fine. This was just fun.

    • 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

       

       

       

  • sizi's avatar
    sizi
    Helper II
    2 years ago

    I have similar requirement however my data contains 8 date columns. I need to date filter to records based on date selection and show records pertaining to filtered date from all date columns . Note all dates will be there. Some dates available some blank. Whichever date is available we need to pull records. 

    data columns are as below as I am not able to paste the data as it's giving error.

     

    Task Name , Assigned date , Planned Start date, Planned end date, Actual start date, Actual end date , Initial Approval date , Technical Approval date , IT clearance date , Final Sign off date. 

    If I select last 1month , all the records that fall under one month irrespective of date columns should show. Kindly help. 

    I tried calendar table and connect the date to planned start date. However when I filter it only considers the records within planned start date selection and not show records that has data within the date selected. 

    Any support is appreciated.

     

    Thanks in advance.