PowerBI Date slicer on a period between Start and End dates

[MariKo]

I am trying to set up a date range filter in order to filter a table with Start and End Dates on certain user-related data. For example, I set up a filter between 22/10/2016 and 06/10/2022 and the table is filtered on all records between these dates based on Start and End Date in the table. In my case, the table does not show exactly what I want since the first line in the table starts on 01/01/2006. My idea that in this case the table shows all lines except the first one.

USER ID	Type Name	Type	Start Date	End Date	Date Included
0001	Contract	03	1 January 2006	31 December 2019	Include
0001	Contract	04	1 January 2020	7 October 2022	Include
0002	Contract	01	30 June 2017	31 December 2019	Include
0002	Contract	02	1 January 2020	7 October 2022	Include
0003	Contract	01	1 August 2021	7 October 2022	Include

I have also used the DAX measures like

Date Included = 
  IF (
FIRSTNONBLANK ( DATA[Start Date], 1 ) <= MAX ( 'Calendar'[Date] ) &&
FIRSTNONBLANK(DATA[End Date], 1 ) >= MIN ( 'Calendar'[Date] ),
"Include",
"Exclude"
  )

and

isShown = 
   IF (
ISFILTERED ( 'Calendar'[Date]),
IF (
    MAX ( 'Calendar'[Date]) >= MAX ( 'DATA'[Start Date] )
        && MAX ( 'Calendar'[Date] ) <= MAX ( 'DATA'[End Date] ),
    1,
    BLANK ()
),
1
 )

with the View Filtering but it does not work. My data consists of a Calendar table and Data table. There are no relationships between the Calendar and Data tables in the model.

Any help would be appreciated! Thank you.

[v-stephen-msft · Accepted solution]

Hi @MariKo ,

Please try this measure:

Date Include = IF(MIN('Calendar'[Date])<=MAX('Table'[Start Date])&&MAX('Calendar'[Date])>=MAX('Table'[End Date]),"Include","Exclude")

Because October 7, 2022 in the end date is outside of October 6, 2022, the maximum date of the slicer, the result I give is also excluded. What logic is it that you only want to show all but the first row? Please describe it in more detail.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MariKo]

Hi, @v-stephen-msft . Thank you for your answer. You solution worked. My logic was to filter on the date range (start date - end date) that fall within the slicer choice.