0

I have a cube with few dimensions. Report Date and Account are two of them. One account id may come under multiple Report dates. I need to find minimum report date for every accountid. Any ideas?

whytheq
  • 34,466
  • 65
  • 172
  • 267
Anubhav Sharma
  • 1

1 Answers1

0

Maybe something similar - 

WITH SET[MinDate] AS
    Head(NonEmpty(
            ReportDate.[ReportDate].[ReportDate].MEMBERS, 
            {(EXISTING [Account].[AccountId].CurrentMember , [Measures].[foo])}
            ),1)

SELECT [MinDate] ON 1,
[Account].[AccountId].MEMBERS ON 0
FROM [bar]
WHERE [Measures].[foo]

If what you want is the minimum date's value, then you would need to get that in a calculated measure instead of a set.

WITH MEMBER Measures.[MinimumDate] AS
    Head(NonEmpty(
            ReportDate.[ReportDate].[ReportDate].MEMBERS, 
            {(EXISTING [Account].[AccountId].CurrentMember , [Measures].[foo])}
            ),1).ITEM(0).Name
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • 1
    Hi Sourav - does that definitely work? I'm not sure if a named set will pick up context ok - you could probably just swap it to a calculated member instead easy enough. Worth testing against AdvWrks. – whytheq Jun 10 '16 at 10:40
  • Rookie mistake!! Fixed it. Also added an approach for the date value, instead of selecting on axis. Thanks for pointing out the error. – SouravA Jun 10 '16 at 12:02