MDX Calc to Get Days In Current Month

All replies

• 

  

  0

  Sign in to vote

  HI Robinson

  You need to have the days in current month attribute  in Date dimension . In Calculation use scope for date dimension and use that attribute in your calculation .

  ---

  Surendra Thota

  Saturday, June 8, 2013 5:48 AM
• 

  

  0

  Sign in to vote

  I'm no good at MDX...can you give me an example
  

  ---

  A. M. Robinson

  Saturday, June 8, 2013 11:01 AM
• 

  

  0

  Sign in to vote

  Hi Robinson

  Create a attribute [Days in Month] in Date Dimension. Then try with is code .

  CREATE MEMBER CURRENTCUBE.[Measures].[Your Measure Name] AS NULL,
  VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'XXXXXX'; 
  SCOPE(([Measures].[Fee Receivables],[Measures].[Revenue]),[Date].[Date].CHILDREN);   
  THIS = ([Measures].[Fee Receivables] / [Measures].[Revenue]) / [Date].[Days in Month].currentmember.membervalue;       
  END SCOPE;   

  
  

  ---

  Surendra Thota

  Saturday, June 8, 2013 3:36 PM
• 

  

  0

  Sign in to vote

  I'm confused as to your solution. You said I need to add a new attribute to the Time dimension call Days in Month. There are thousandss of rows in my Time dimension. I can add a column just fine, but how does it get populated?? You don't state how this days in month field gets populated

  I'm not good with MDX, but wouldn't a solution look something like some kind of COUNT on the days in a month - or the CURRENT MEMBER?

  ---

  A. M. Robinson

  Saturday, June 8, 2013 4:43 PM
• 

  

  1

  Sign in to vote

  Hi Robinson

  how you populated data in Date dimension table ? in your date dimesion you will have year and month columns, based on these columns you fill value in DaysinMonth .if it is leap year you will have 29 days in feb and remaining months values are same for any year.

  ---

  Surendra Thota

  Saturday, June 8, 2013 5:02 PM
• 

  

  0

  Sign in to vote

  The Time dimension is automatially generated by SSAS when you choose the "Create New Dimenson" dialog and choose to create a Time dimension...once created, that's it.

  This is how a Time Dimension is created:

  

   

  There is no option for Days I a Month. My time periods run from 2005 to 2018. That's 13 years.

  There must be a way to say "here is the month/year I have chosen. Give me the number of days"...without me having to alter my Time table.

  ---

  A. M. Robinson

  
  
  

  • Edited by ansonee Saturday, June 8, 2013 8:27 PM new

  Saturday, June 8, 2013 8:13 PM
• 

  

  3

  Sign in to vote

  A much more straightforward approach that will perform better is creating a measure group off the DimDate table. Just create a single Count measure and it will do what you want with better performance than any MDX approach.

  ---

  http://artisconsulting.com/Blogs/GregGalloway

  • Proposed as answer by Ed Price - MSFTMicrosoft employee Sunday, June 9, 2013 9:50 PM
  • Marked as answer by ansonee Monday, June 10, 2013 2:26 PM

  Sunday, June 9, 2013 12:08 PM
• 

  

  0

  Sign in to vote

  Good solutions here. See if you want to contribute these (or others) to the TechNet Guru Competition announced here: http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/ff49e4a4-1e28-4afa-ad04-04b82961f89d

    

  Here's more on the TechNet Guru awards: http://social.technet.microsoft.com/wiki/contents/articles/17625.technet-guru-contributions-june-2013.aspx

  Thanks!

  ---

  Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)
  
  Answer an interesting question? Create a wiki article about it!

  Sunday, June 9, 2013 9:50 PM
• 

  

  0

  Sign in to vote

  Furmangg:

  Thank you!!! Your solution was PERFECT!!! I never would have thought of that!!!

  Thanks again!!!

  ---

  A. M. Robinson

  Monday, June 10, 2013 2:26 PM
• 

  

  0

  Sign in to vote

  Great solution from GregGalloway, it worked perfectly here. 

  Tuesday, February 5, 2019 10:31 AM