Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Filter in between dates

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...
Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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.

   

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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)))