mdx get measures for a date range between startDate and endDate

Question

I need to know what is the rate of booking of beds (Like in an hotel).

The number of beds (summed month per month) for a range of dates, for the bookings that are in the range (including the partial sum of bookings for the dates that are partially in the range)

I created a "booking" fact table with a StartDate and a EndDate with a measure "countSejoursDate" (count(rows)) and a measure "NbrOfBeds" (sum). I created 2 "wizard time" dimensions linked as following :

I also created a 3rd "wizard time" dimension called "Date" not linked to any fact.

While trying to get the result, using the MDX below, I'm just able to retrieve the count of rows inside a range of dates... but even with this, the value of the 1srt day of each month is false!

with member nbsejsDate as AGGREGATE( 
          {NULL:LINKMEMBER([Date].[Calendrier].CURRENTMEMBER,[START_DATE].[Start_Calendrier])}
        * {LINKMEMBER([DATE].[Calendrier].CURRENTMEMBER, [END_DATE].[End_Calendrier]):NULL}
        , [Measures].[countSejoursDate])
select nbsejsDate
on 0 
, [Date].[Calendrier].[Jour].&[2015-03-01]:[Date].[Calendrier].[Jour].&[2015-03-31] on 1
from [Cube]

ic3 ic3 · Accepted Answer · 2016-04-04T13:12:36

It's a bit strange as what we've here is an many-to-many relation in the form of a start and end date. Trying to make a correct calculation relying on MDX calculation instead of using a many-to-many relation it's tricky and very,very error prone.

There are different possibilities for solving this :

Use a Range (From - To) link type in the link of time dimension in the Facts.
Use a Javascript view to create an new column that is an array of dates (start/end). The many-to-many relation is created on the fly.

This should make a lot easier any calculation, if I understood the problem correctly.

hope it helps

mdx get measures for a date range between startDate and endDate

1 Answers