Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to give back to the community!
Thank You!
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
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
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
Proud to give back to the community!
Thank You!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |