Date Filter in between dates

[Anonymous]

Hi,

I have a start date and an end date column in my bookings table. Currently the relationship runs to only the start date.

This is therefore currently only displaying start dates within the relative date filter that I'm displaying. What I want to do is when the two date filters are selected by the user, it displays any booking within the start date and end date column. Would somebody be able to please help?

My aim is to ensure the example below is catered for in the report.

Thanks in advance

Liam

[amitchandak]

@Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Check if this blog can help : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Appreciate your Kudos.

[Anonymous]

Hi @amitchandak ,

In the images below, because the Start Date of 19/05/2019 and End Date of 31/12/2020 is within the date filter of 01/10/2019 and 31/03/2020, this should display in the table.

That's what I'm trying to do. Currently it works only if 19/05/2016 (Start Date) is within 01/10/2019 and 31/03/2020 which therefore wouldnt display.

   

[mahoneypat · Accepted solution]

Because you have a relationship with the StartDate, it is filtering the data only based on that column.  To get the functionality you are looking for you need to take that relationship off the table by one of a few ways -

1. Deleting that relationship (probably not recommended if you need to do other analyses on StartDate)

2. Add a new Date table with DAX to be used only in your slicer with something like SlicerDates = VALUES('Date'[Date]) //or whatever you Date column is

3. Use CROSSFILTER() in a calculate to turn off that relationship just for one measure

#2 if probably the simplest.  If you do that, you can then use a measure like this in your table visual (or as a Filter on your table visual).  Replace "Table" with your actual table name.

Show In Table =
VAR __minslicer =
    MIN ( SlicerDates[Date] )
VAR __maxslicer =
    MAX ( SlicerDates[Date] )
RETURN
    IF (
        ISBLANK (
            COUNTROWS (
                FILTER (
                    Table,
                    Table[Start Date] <= __maxslicer
                        && Table[End Date] >= __minslicer
                )
            )
        ),
        1
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat ,

Many thanks for your reply.

I think thats what I need, except, I may have described it badly but where in the formula it says > Start Date and < End Date.

I would need it so if any part of the date crosses then it needs to be included in my matrix.  I hope that makes sense?

As below, it starts in 2016 and ends greater than 31/03/2020 (31/12/2028), but because it includes it somewhere in its timeline it would need to be there.

Thanks again 

[mahoneypat]

Please let me know if you tried the one I suggested.  By doing Start < Max and End>Min should include anything that is "Active" over the slider date range.  If you want only things that Started and Stopped within the date range, just change it to Start>=Min and End <=Max.

Regards,

Pat

[Anonymous]

Thankyou @mahoneypat for your help

[amitchandak]

@Anonymous , One of the solution was there in my HR blog where we have Start date end date join to the same date calendar.

This one try with a date table not joined or use cross filter

measure = 
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate(sum(Table[Data]), filter(Table,(Table[Start Date]<=_max && Table[Start Date]>=_min )|| ( Table[end Date]<=_max && Table[end Date]>=_min)))
///////////////////Or

measure = 
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate(sum(Table[Data]), filter(Table,(Table[Start Date]<=_max && Table[Start Date]>=_min ) && ( Table[end Date]<=_max && Table[end Date]>=_min)))

[Anonymous]

Hi @amitchandak 

Thankyou, thats a great help.

Regarding where it says greater than start date and less than end date, do you know how this could be so if any point of the start and end dates touch the dates in the filter then include?

Thanks

Liam

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.