How to Find the records between two columns which are start date and end date

[KaranMane]

there is another column which is named as status (which contains active and closed status). I want the status which are in the range of dates which i picked by slicer date picker.

Note : there are two separate column for start date and end date. and after selecting the dates in slicer then status should be filtered out.

  Thank you in advance 

[v-yadongf-msft · Accepted solution]

Hi @KaranMane ,

This is my test table:

Please create a calendar table:

CalendarTable = ADDCOLUMNS(CALENDAR(MIN('Table'[Start date]),MAX('Table'[End date])),"Month",MONTH([Date]))

Relationship:

Create a slicer from calendar table:

I think you will get the result you want:

Best regards,

Yadong Fang

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

[Ashish_Mathur · Accepted solution]

Hi,

The ideal approach should be to create one row for each date between the start and end date.  This however may lead to a lot of rows being created.  We can reduce the table size by creating one row for each month between the start and end date.  This will allow you to select only one/multiple months (not random dates).  Are you OK with selecting months in a slicer rather than a specific date range?

[v-yadongf-msft · Accepted solution]

Hi @KaranMane ,

This is my test table:

Please create a calendar table:

CalendarTable = ADDCOLUMNS(CALENDAR(MIN('Table'[Start date]),MAX('Table'[End date])),"Month",MONTH([Date]))

Relationship:

Create a slicer from calendar table:

I think you will get the result you want:

Best regards,

Yadong Fang

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