cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zina-aziz Frequent Visitor
Frequent Visitor

Creating a date filter for a date range

Hi everyone,

 

I am working with hotel data which contains nightly rates per hotel with a validity date range as follows:

 

Hotel El Aurassi180.0001-Jan-1731-Dec-17
Epic Sana Luanda190.0001-Feb-1731-Nov-17
Alvear Palace Hotel220.0001-Mar-1731-Oct-17
Alvear Palace Hotel290.0001-Jan-1731-Dec-17
Casasur Bellini Hotel130.0001-Feb-1731-Nov-17
CasaSur Recoleta Hotel190.0001-Mar-1731-Nov-17
Dazzler Tower Recoleta85.0001-Jul-1731-Aug-17
Faena Hotel Buenos Aires195.0001-Jan-1931-Dec-19
Four Seasons Hotel Buenos Aires218.0001-Jan-1831-Dec-18

 

I am trying to create a date filter, so when I select for example January 2017, it will only show me the hotels and the rates that are valid for that month. Is this possible? If so how do I go about creating it?

 

Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Creating a date filter for a date range

Hi,

 

You may download my solution PBI file from here.

 

Hope this helps.

 

Untitled.png

7 REPLIES 7
Super User
Super User

Re: Creating a date filter for a date range

Hi,

 

You may download my solution PBI file from here.

 

Hope this helps.

 

Untitled.png

zina-aziz Frequent Visitor
Frequent Visitor

Re: Creating a date filter for a date range

Thank you so much Ashish.

 

The solution you have created is exactly what I am after, however I am finding it hard to recreate in my master data file. Would you be generous as to provide me with an explanation of how you achieved it? I am a beginner to Power BI, so a step by step would be much appreciated.

 

Thank you again.

 

Zina

Super User
Super User

Re: Creating a date filter for a date range

Hi,

 

You are welcone.  I suggest that you go to Home . Edit Queries and look at each of the Applied steps on the right hand side pane.  If you are unclear about any step, please feel free to post back.

zina-aziz Frequent Visitor
Frequent Visitor

Re: Creating a date filter for a date range

Thank you. 

 

I am getting this error message. I am not sure what it means.

powerbi.JPG

 

 

Super User
Super User

Re: Creating a date filter for a date range

Hi,

 

The simple formula in the Query Editor should be:

 

={Number.From([From])..Number.From([To])}

zina-aziz Frequent Visitor
Frequent Visitor

Re: Creating a date filter for a date range

Thank you it is working now.

 

The calender, is that a table? Also how do i add the month, order and year components underneath it? Are they measures?

 

POWERBI II.JPG

Super User
Super User

Re: Creating a date filter for a date range

Hi,

 

Yes, Calendar is a Table.  To extract the Year and Month from a date, use these calculated column formulas

 

=YEAR(Calendar[Date])

=FORMAT(Caledar[Date],"mmmm")

 

Hope this helps.