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

Date range determines appropriate values

Hi all,

 

I have a report calling a fixed room count per property from my properties table:

PropTable_RoomCountSpecifiedbyProp.JPG

Each row is dedicated to an individual property, and the room count available on a daily basis specified. Using the below measures & filters I am able to calculate the rooms available in a filterable date period:

Measures&Filters.JPG

However, I now have to contend with room values changing over time. Is there a means of calling the different values per days in the filtered date range, without dedicating individual property columns to my date table (see last pic), using something along the lines of an accompanying RoomCount table? see below for example:
PropTable_RoomCountSpecifiedbyDateRange.JPG 

Using "Property 1" as an example, the calculation would essentially be able to discern, where a filtered date range of 01/01/2014-10/01/2014 was specified, that ((6 days*20 rooms)+(4 days*50 rooms)) was the desired calculation.

 

Again, trying to avoid the requirement for the below, as the date range and each additional property added will add to the data source size and impact calculation + response time.

 DateTable_RoomCountSpecifiedbyProp.JPG

 

All feedback appreciated.

 

Thanks!

PBI_V

3 REPLIES 3
Community Support Team
Community Support Team

Re: Date range determines appropriate values

Hi @PBI_V,

 

I'd like to suggest you create a calendar table with full date range, then use calendar date and property name column to create matrix with measure as value field.(calendar not has relationship to original table)

 

Sample table:

Calendar =
CALENDAR ( MIN ( VALUES ( Table[From] ) ), MAX ( VALUES ( Table[To] ) ) )

 Measure:

RoomCount =
VAR _currentDate =
    SELECTEDVALUE ( Calendar[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Room Count] ),
        FILTER ( ALL ( Table ), _currentDate IN CALENDAR ( Table[From], Table[To] ) ),
        VALUES ( Table[Property Name] )
    )

 

In addition, you can also try to expand date column to getdate date to create visualization.

Convert date ranges into list of dates?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
PBI_V Frequent Visitor
Frequent Visitor

Re: Date range determines appropriate values

Hi Xiaoxin!

 

This is looking promising so far!

 

Can I ask a few questions regarding the solution to date:

 

  1. Is the Calendar table required if I have an existing Date Table in my model, spanning the From and To dates specified?
    If not can you suggest how this might change the measure to utilise an existing date table?
  2. The measure works great but only when utilising the dates from the Calendar table, even when a relationship is created between the Calendar and Date Table?
  3. Unfortunately the measure only works when specifiying the Room Count on a daily basis, it does not appear to facilitate summarisation at week, month, year level etc. can this be achieved?

Again, @v-shex-msft thanks for the help so far, it is definitely progressing.

 

Kind regards

PBI_V

  

 

 

Community Support Team
Community Support Team

Re: Date range determines appropriate values

Hi @PBI_V,

 

#1. You can also use existed calendar table to replace steps and formula what I told about.

 

#2. Nope, calendar only used to get current date value to compare with two date column from fact table, relationship not the required.

 

#3. Unfortunately, 'in' operation not works to compare many to many records, it only support one to many.

 

It you want to compare with summarized records, I think create a expand table should be suitable for your requirement.

Spread revenue across period based on start and end date, slice and dase this using different dates

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |