Handling a Interval Match

[Senarath]

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.

[fhill · Accepted solution]

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

[Phil_Seamark · Accepted solution]

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

[gabrielvigo]

Very good resolution.

But, I can not use this in a KPI (DistinctCount).

How can it be done?

[Senarath]

Thanks @Phil_Seamark and @fhill for your correct replies.

[fhill · Accepted solution]

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