CODEWITHSUNDEEP
datetimessasmaxmdxmin
user2395631
user2395631

Reputation: 11

MDX for finding the time at which the the max value occured

I am new to MDX and I am trying to find a way to write it in such a way that I can get the date and time value from my datetime dimension of when a measure's max and min values occured. Like I found out the max and min of my actual values by adding them in the cube as a measure. Right click on actuval value and in properties selecting max and min. But I dont know how to specify to give me the time when it happened in a given start adn end period of teh query. My query looks like this right now, I need to add two more measurres to show the date adn time when the max value actual and min value actual happened.

Select {  [Measures].[ItemKey],[Measures].[UTC],   
    [Measures].[Value Actual],  [Measures].[Min Value Actual], [Measures].[ Max Value Actual]   
    } on columns ,
    {[Dim_Item].[ItemId].&[63678],[Dim_Item].[ItemId].&[63710]} 
    on rows  from [Energy Aggregator] 
    Where 
     ([Dim_DateTimeLocal].[CalenderLocalDateTime].
     [HourofDay].&[26]&[2012]&[12]&[21]&[6]&[0]: 
     [Dim_DateTimeLocal].[CalenderLocalDateTime].[HourofDay].
     &[26]&[2012]&[12]&[25]&[3]&[0].lag(1)
    )

I have been stuck on this for a while, any help would be greatly appreciated. Thanks -Sarah

Upvotes: 1

Views: 656

Answers (1)

Meff
Meff

Reputation: 5999

Try creating a calculated member that's the TOPCOUNT (1) of the date dimension using the value measure. Might need some work to get it to play nice with filters & slicers.

Upvotes: 1

Related Questions