Average of a Daily count of a measure over a number of Months

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  bi query languages (mdx, dmx)   » Average of a Daily count of a measure over a number of Months

Average of a Daily count of a measure over a number of Months

Topic RSS Feed

Posts under the topic: Average of a Daily count of a measure over a number of Months

Posted: 11/3/2011

RDavis
RDavis
Jedi Youngling 2  points  Jedi Youngling
  • Joined on: 8/20/2010
  • Posts: 1

I'm trying to create a calculation that will calculate an average of a daily count, I've tried many different things and have gotten the close to what I want but can't quite get it correct.

The closest I've gotten is the following (using 02/28/2011 as current member)

WITH  

 

MEMBER [Measures].[Count_90D2] AS  SUM([Date].[Month].CurrentMember.Lag(0) : [Date].[Month].CurrentMember, [Measures].[Patient Day Count])/COUNT([Date].[Date Key].CurrentMember.Lag(27) : [Date].[Date Key].CurrentMember)

Which gives me the correct number for a 27 day range.  However I need to get this to work for 3 Mo, and 12 Mo by changing the CurrentMember.lag(x) to 2 and 11 respectively to get there current month plus the previous x months.  I however can't figure out how to get the number of days in the divisor to be correct as I can't just change the lag there.  I need the set of all days from the Current Member Month - 2 but I'm at a loss at how to get that result.  Any thoughts on how to solve this issue?
Page 1 of 1 (1 items)