Share via

Prior Month To Date (MTD) calculation with DAX

  • Article

Question

Saturday, February 9, 2013 8:04 PM

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 (5)

Saturday, February 9, 2013 10:38 PM ✅Answered | 1 vote

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

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 9999 are invalid.

Thanks again for your help.

Bill

Tuesday, June 18, 2013 4:36 PM

after 1999 its invalid???

or its 9999?

I got the same error while my dates are between 1935 and 9999...

I change the max date to 2100 and the error disappear for me.

but I don't understand why a valid date <= 9999- 12-31 which is indicated as valid for tabular raise the error...

Thursday, June 4, 2015 11:34 PM

Hi,

i've found this post through an internet search. I also pretend to do a Prior MTD calculation on distinct customers and i've tried many versions without success.

My situation is this: i have a fact table 'fct sales' that connects to a time dimension 'dim posting date' through an int 'sk_posting_date', and the time dimension has a date value in 'date' attribute, which, from what i've been reading, is a bad idea, i read that it this relation should be done through a date 'sk_posting_date'. I'd like to know if this is mandatory or recommend, to easy dax calculations related to time.

the time dimension is continuous.

i've tried this in model.bim and immediatly had results in model.bim.

Nr Distinct Customers Month -1:=CALCULATE(DISTINCTCOUNT([SK_CUSTOMER]); DATEADD('Dim Posting Date'[Date];-1;MONTH)) result is 4427, equal to the actual MTD

Nr Distinct Customers Month -1 v2 Marco:=CALCULATE(DISTINCTCOUNT('Fct Sales'[SK_CUSTOMER]);DATESINPERIOD('Dim Posting Date'[Date];MAX('Dim Posting Date'[Date]); -1; MONTH )) result is blank

Nr Distinct Customers Month -1 vt5:=CALCULATE(DISTINCTCOUNT([SK_CUSTOMER]); PARALLELPERIOD('Dim Posting Date'[Date];-1;MONTH)) result is also 4427

Nr Distinct Customers Month -1 vt7:=CALCULATE(DISTINCTCOUNT([SK_CUSTOMER]); DATESBETWEEN('Dim Posting Date'[Date];FIRSTDATE('Dim Posting Date'[Date]);LASTDATE('Dim Posting Date'[Date]))) also 4427

Nr Distinct Customers Month -1 vt8:=CALCULATE(DISTINCTCOUNT([SK_CUSTOMER]);
                  DATESBETWEEN('Dim Posting Date'[Date];
                                  FIRSTDATE(DATEADD('Dim Posting Date'[Date];-1;MONTH));
                                  LASTDATE(DATEADD('Dim Posting Date'[Date];-1;MONTH))
                  )) also 4427

Nr Distinct Customers Month -1 v3:=CALCULATE(DISTINCTCOUNT('Fct Sales'[SK_CUSTOMER]);PARALLELPERIOD('Dim Posting Date'[Date];-1;MONTH);ALL('Dim Posting Date')) also 4427

So, i'd like to know what am i doing wrong, and if must create a date column on my 'fact sales' to easy the calculations.

Kind regards

Tuesday, June 23, 2015 7:04 AM

Hi Lrmmf_,

It is not mandatory to have the relationship to the date table on a date column, but if you use an integer column like you do, you should use the 'Mark as date table' option which can be found in the Design tab.

regards