Forum Discussion
Rows between two dates
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.
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.
10 Replies
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
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.
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.
Hi,
I still think creating one row for every date in the Calendar Table is the best way out.
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
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?
Hello? Can anyone help me? This problem has NOT been fixed. I feel we are close to a solution.
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])returnIF(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?
I created a new test example power bi report file for this issue. How do I attach a .pbix file to this post?
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.
