Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Prior Month To Date (MTD) calculation with DAX

Respondido Prior Month To Date (MTD) calculation with DAX

  • Saturday, February 09, 2013 8:04 PM
     
     
    Sign In to Vote
    0

    I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates.  Is this a bug or can anyone explain why it is working this way?  Below is an image of my pivot table and I have outlined the values in red that are in question.

    Below are my DAX formulas used each column visible in my image:

    • Distinct Count of Events:=DISTINCTCOUNT([EventID])
    • CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL(dimDate))
    • PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate))
    • ParallelMonth:=CALCULATE([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))

    I appreciate any help on this.

    Regards,

    Bill

     

All Replies

  • Saturday, February 09, 2013 10:38 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Looks like this is caused by using the Time Intelligence functions against a table of data without a full set of dates (ie, DATESMTD(Events[EventDate]))

    Suggest you pass the date column from your dimDate table to the DATEMTD function.  Assuming your dimDate table has a full set of dates (all of 2013).  This should allow you to remove your second filter for ALL(dimDate).

    Like this:

    PriorMTD:=CALCULATE([Distinct Count of Events]
                                   ,DATEADD(DATESMTD(dimDate[Date])
                                            ,-1
                                            ,MONTH
                                           )
                                   )

    Let me know if that helps.  And for more details on Time Intelligence functions like this, check out this thorough blog post on the subject from Jeffrey Wang (MS Product Team).

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com






     
  • Sunday, February 10, 2013 10:29 PM
     
      Has Code
    Sign In to Vote
    0

    Brent,

    Thanks for your help.  You were right that my Events[EventDate] was not a full set of dates.  The Events table is my fact table. 

    The document that you referenced did help me understand why I got the results that I did.  Here is my interpretation.  DAX has an internal calendar data structure and if your table isn’t complete then it will make a guess whether to move whole months based on the days that exist.  Since 2/8/2013 was the last day in February in my Events table it guessed that I wanted the last day of the previous month.

    I initially did get an error that I think is worth noting.  The error was:

    ERROR - CALCULATION ABORTED: Calculation error in measure 'Events'[PriorMTD]: 
    An invalid numeric representation of a date value was encountered.

    This error was a result of having dates going back to 1/1/1900 in the dimDate table.  After some research I discovered that dates before March 1<sup>st</sup> of 1900 and after December 31<sup>st</sup> of 1999 are invalid.

    Thanks again for your help.

    Bill