cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KaranMane
New Member

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

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.

IMG_20221123_131237~2.jpg

IMG_20221123_131328.jpg

  Thank you in advance 

2 ACCEPTED SOLUTIONS
v-yadongf-msft
Community Support
Community Support

Hi @KaranMane ,

 

This is my test table:

vyadongfmsft_0-1669260072001.png

 

Please create a calendar table:

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

vyadongfmsft_1-1669260119016.png

 

Relationship:

vyadongfmsft_2-1669260154247.png

 

Create a slicer from calendar table:

vyadongfmsft_3-1669260192552.png

 

I think you will get the result you want:

vyadongfmsft_4-1669260223112.png

vyadongfmsft_5-1669260249189.png

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.

View solution in original post

Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yadongf-msft
Community Support
Community Support

Hi @KaranMane ,

 

This is my test table:

vyadongfmsft_0-1669260072001.png

 

Please create a calendar table:

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

vyadongfmsft_1-1669260119016.png

 

Relationship:

vyadongfmsft_2-1669260154247.png

 

Create a slicer from calendar table:

vyadongfmsft_3-1669260192552.png

 

I think you will get the result you want:

vyadongfmsft_4-1669260223112.png

vyadongfmsft_5-1669260249189.png

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.