Forum Discussion
Handling a Interval Match
Hi,
I have a requirement like below.
Check In Date Check Out Date Name Hotel Name
13-Jul-2017 20-Jul-2017 John Hilton Madrid
15-Jul-2017 17-Jul-2017 Nancy Galadari
My requirement is if I want to see how many guests are checked In on a particular date e.g 15-Jul-2017, above two guests should be shown. I used to handle it by Interval match function and no idea how to handle in Power BI. Let's assume I'm loading date both from an Excel file and by a SQL query to the model.
Check out this post, and follw their steps. Your filter will be on the newly created column, just make sure your original Start/End Dates and whatever other data you need are in the graph/chart (Not the newly created dates.)
https://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/td-p/60577
Hi Senarath
One approach is to do the following,
1. Create a basic Date table using CalendarAuto() but don't relate it to your data table. In my case I called this table 'Slicer Dates' and I created a slicer over it.
2. Create the following measure which you can use to filter your data
Filter Measure = IF( MIN('Table1'[Check in Date]) <= MIN('Slicer Dates'[Date]) && MIN('Table1'[Check out Date]) > MIN('Slicer Dates'[Date]) , 1 , 0)3. Drag the new measure to the Visual Filters of your grid and set it that it has to be 1
There is a PBIX File here
https://1drv.ms/u/s!AtDlC2rep7a-kHAdJz1jWGkPvUTz
4 Replies
Hi Senarath
One approach is to do the following,
1. Create a basic Date table using CalendarAuto() but don't relate it to your data table. In my case I called this table 'Slicer Dates' and I created a slicer over it.
2. Create the following measure which you can use to filter your data
Filter Measure = IF( MIN('Table1'[Check in Date]) <= MIN('Slicer Dates'[Date]) && MIN('Table1'[Check out Date]) > MIN('Slicer Dates'[Date]) , 1 , 0)3. Drag the new measure to the Visual Filters of your grid and set it that it has to be 1
There is a PBIX File here
https://1drv.ms/u/s!AtDlC2rep7a-kHAdJz1jWGkPvUTz
Thanks Phil_Seamark and fhill for your correct replies.
Very good resolution.
But, I can not use this in a KPI (DistinctCount).
How can it be done?
Check out this post, and follw their steps. Your filter will be on the newly created column, just make sure your original Start/End Dates and whatever other data you need are in the graph/chart (Not the newly created dates.)
https://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/td-p/60577
