November 25, 2010 at 6:51 am
I have a cube with measure "Chrg" on Products and a Date Dimension with "EffectiveDate". I need help creating calculated measures which report the difference in the "Chrg" between the earliest and the latest "EffectiveDate". I am new with MDX and was thinking somehow there was a way to reference the min and max of the "EffectiveDates" selected. Can someone help and/or point me to a reference? Thanks in advance.
November 27, 2010 at 12:03 pm
I’m not real great with MDX yet either but I’ll throw a couple things out there. I believe there are MIN and MAX functions but normally used with measures so I don’t know if that would help with your EffectiveDate. Have you looked at some of the hierarchy functions like FirstChild, LastChild or FirstParent and Last Parent?
One way to find the last date of your time dimension that contains any data is something like this:
Tail(Filter([Time].[EffectiveDate].Members,
Not IsEmpty(Time.CurrentMember)),1).Item(0)
This assumes you have a date dimension called “Time”. Replace the names with what fits for your cube. There is also a Head function you could play around with.
It might be helpful to include more information about your dimensions. Is the EffectiveDate a member of a Date dimension and what does the hierarchy look like? Do you have any code that gets you close?
The above expression by the way came from the book “Fast Track to MDX” which is highly recommended.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply