cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Senarath
Frequent Visitor

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
fhill Solution Sage
Solution Sage

Re: Handling a Interval Match

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

 

Capture.PNG

View solution in original post

Microsoft
Microsoft

Re: Handling a Interval Match

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

 

magic filtering.png

 

There is a PBIX File here

 

https://1drv.ms/u/s!AtDlC2rep7a-kHAdJz1jWGkPvUTz

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
fhill Solution Sage
Solution Sage

Re: Handling a Interval Match

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

 

Capture.PNG

View solution in original post

Microsoft
Microsoft

Re: Handling a Interval Match

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

 

magic filtering.png

 

There is a PBIX File here

 

https://1drv.ms/u/s!AtDlC2rep7a-kHAdJz1jWGkPvUTz

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Senarath
Frequent Visitor

Re: Handling a Interval Match

Thanks @Phil_Seamark and @fhill for your correct replies.

gabrielvigo Helper I
Helper I

Re: Handling a Interval Match

 

Very good resolution.

 

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

How can it be done?

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors