Creating a date filter for a date range

[zina-aziz]

Hi everyone,

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

Hotel El Aurassi	180.00	01-Jan-17	31-Dec-17
Epic Sana Luanda	190.00	01-Feb-17	31-Nov-17
Alvear Palace Hotel	220.00	01-Mar-17	31-Oct-17
Alvear Palace Hotel	290.00	01-Jan-17	31-Dec-17
Casasur Bellini Hotel	130.00	01-Feb-17	31-Nov-17
CasaSur Recoleta Hotel	190.00	01-Mar-17	31-Nov-17
Dazzler Tower Recoleta	85.00	01-Jul-17	31-Aug-17
Faena Hotel Buenos Aires	195.00	01-Jan-19	31-Dec-19
Four Seasons Hotel Buenos Aires	218.00	01-Jan-18	31-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!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[zina-aziz]

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

[Ashish_Mathur]

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]

Thank you. 

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

[Ashish_Mathur]

Hi,

The simple formula in the Query Editor should be:

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

[zina-aziz]

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?

[Ashish_Mathur]

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.