Forum Discussion

PBI_V's avatar
PBI_V
Frequent Visitor
8 years ago

Date range determines appropriate values

Hi all,   I have a report calling a fixed room count per property from my properties table: Each row is dedicated to an individual property, and the room count available on a daily basis spe...
PBI_V's avatar
PBI_V
Frequent Visitor
8 years ago

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, Anonymous thanks for the help so far, it is definitely progressing.

 

Kind regards

PBI_V

  

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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