locked
MDX Calc to Get Days In Current Month RRS feed

  • Question

  • I have a calculation that is going to need the number of days in the current month - including weekends. So, the value for January would be 31, April would be 30, etc.

    The entire calculation is:

    ([Measures].[Fee Receivables] / [Measures].[Revenue]) / [Measures].[Days in Current Month]

    Any insight would be greatly appreciated!

    Thanks!!


    A. M. Robinson

    Saturday, June 8, 2013 2:18 AM

Answers

All replies

  • 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
  • I'm no good at MDX...can you give me an example

    A. M. Robinson

    Saturday, June 8, 2013 11:01 AM
  • 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
  • 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
  • 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
  • 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
  • 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

    Sunday, June 9, 2013 12:08 PM
  • 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
  • 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
  • Great solution from GregGalloway, it worked perfectly here. 
    Tuesday, February 5, 2019 10:31 AM