MDX - Concurrent calculations based on a "record time range"?

Question

Dear MDX experts,

- Is it possible to get the concurrent calculation based on a record time range?

Lets say I have; 'start date', 'end date', 'used', and 'color' available... in my fact table..

Is it possible to get the concurrent of 'used' per time (the biggest sum of 'used' that happened during the same range), if yes - what about concurrent used per 'color'?

Answer 1

To get the sum of all used over a time period, you can use the sum function.

Moreover, this will take into account the CurrentMember of Color, so it will be per member, whatever selection you choose.

This MDX is a starting point:

with member [Measures].[TotalUsed] as
    sum({[Date].[YQM].&[20090501]:[Date].[YQM].&[20090907]}, [Measures].[Used])

select
    {[Measures].[Total Used]}
on columns,
    {[Color].[Colors].[All].MEMBERS}
on rows
from [Cube]

I think this is what you're looking for, but please clarify in comments if it doesn't meet your needs.