Date filter

[pva]

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:

Sprint	Start date	End date
1	2021/12/31	2022/01/15
2	2021/07/31	2021/12/15
3	2021/02/20	2021/04/12
4	2021/12/07	2021/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

Sprint	Start date	End date
1	2021/12/31	2022/01/15
2	2021/07/31	2021/12/15
4	2021/12/07	2021/12/31

Thanks for your support.

Pedro

[sizi]

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. 

[parry2k]

@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]

@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

[bcdobbs]

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]

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.

[parry2k]

@bcdobbs That's exactly what I was going to do, expand the table. Nothing is better if you get the data in the granularity in which you want to view your report. I will still give a few more ideas to DAX. You have done awesome @bcdobbs . Thanks for sharing all this.

Best,

P

[parry2k]

@pva and here is the output:

[parry2k]

@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]

Nice solution @parry2k !

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

[bcdobbs]

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 · Accepted solution]

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: