Rows between two dates

[MrMike]

In Power BI desktop, how to show rows that are between a start date and end date? Also which visualation to use? Is slicer the best option?

For example, my data is like this:

Start Date	End Date	Course
1/1/2021	2/1/2021	Math
2/1/2021	3/1/2021	Science
3/1/2021	4/1/2021	English
4/1/2021	5/1/2021	Art

The user selects a start date and end date. For example the user selects:

Start	2/15/2021
End	3/20/2021

Then in the table of the power bi report only the rows Science and English are shown.

What visualization should I use for the user to select the Start date and End date?

Also how do I set up the Power BI report to only show rows where the row Start date and End date are with in the user selected Start date and End date?

Also I looked at other Power BI posts and YouTube videos and they say to create a calendar table, but that doesn't make sense why do I have to create a table to store every date in a 20 year span, that is 7305 rows just to show every day from 1/1/2021 to 1/1/2041.

[Anonymous · Accepted solution]

Hi  @MrMike  ,

Here are the steps you can follow：

1. Create measure.

Flag1 =
var _selectedperiod=
SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])
return
IF(MAX('Table'[Start Date]) in _selectedperiod||MAX('Table'[End Date]) in _selectedperiod||
(MAX('Table'[Start Date])<=MIN('date'[Date])&&MAX('Table'[End Date])>=MAX('date'[Date]))
,1,0)

2. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[MrMike]

I created a new test example power bi report file for this issue. How do I attach a .pbix file to this post?

[Anonymous]

Hi  @MrMike ,

Here are the steps you can follow：

1. Created a calendar table using calculated table

date = CALENDARAUTO( )

2. Create measure.

Flag1 =
var _selectedperiod=
SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])
return
IF(MAX('Table'[Start Date]) in _selectedperiod||MAX('Table'[End Date]) in _selectedperiod,1,0)

3. Set Flag1 is 1. in Filter.

4. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[MrMike]

Still problem. I think we are close to solving this. The DAX needs be like Start Date is equal to or greater than the selected date range AND End Date is equal to or less than the selected date range. How do I code that?

Something like 

Flag1 =

var DateRange = SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])

return

IF(MAX('Table'[Start Date]) >= DateRange & MAX('Table'[End Date]) <= DateRange, 1, 0)

but Power BI gives me error becuase my synax is wrong, so what is the right way?

[MrMike]

There is still a problem. I added a new row to the table Start Date 1/1/2021 End Date 12/1/2021 Course: Gym

When I select Start Date 2/1/2021 and End Date 3/1/2021 only Math, Science and English show (better than before but still not working) because I expect to see 4 classes now: Math, Science, English and Gym (the new row I added).

Thank you for providing the PBIX file that helped. So again, I added a new row to your table and it should show in the visualization table when I select Start Date 2/1/2021 and End Date 3/1/2021 because the new row Gym goes for the whole year. The new row does show when I select 1/1/2021 and End Date 12/1/2021

Seems the dax code is only looking at the max start date and max end date but should be equal to or greater than start date and equal to or less than end date. How do I code that?

[MrMike]

Hello? Can anyone help me? This problem has NOT been fixed. I feel we are close to a solution.

[Ashish_Mathur]

Hi,

I still think creating one row for every date in the Calendar Table is the best way out.

[timg]

Hi,

Regarding your question about showing data between a start and end date, the following link explains how you can do this through slicers: https://radacad.com/from-and-to-date-slicers-in-power-bi-filtering-based-on-two-fields

Regarding your question about why a calendar table is recommended, there are several reasons, some of which are:

• You can use it to join multiple tables together and analyze them together (e.g. you can filter all tables in your model with a single slicer
• It reduces redundant data (e.g. you have 1x a column for date, year, month, quarter, etc. In your model Instead of having these columns in multiple tables.
• It allows you to make calculations that require all days to be present in the dataset 
• Etc.

Best regards,

Tim

[MrMike]

I read the article and created custom calendar date table. The results are still wrong. When I select Start Date 2/15/2021 and End Date 3/20/2021 no rows in table show, I expect to see Science and English.

[MrMike]

Also when I select Start Date 2/1/2021 and End Date 3/1/2021 only Science shows. I would expect to see Math, Science and English, because those 3 classes are within the date range 2/1/2021 and 3/1/2021.