Custom "TO DATE" value for MTD, QTD, YTD functions


  • Hi,

    I want to be able to use the "TO DATE" functions in DAX but I need to specify an arbitrary "end date".

    For example, today is January 2, 2014 (Happy New Year)

    I want to determine the Month To Date value for November 18, 2013.  That is I want to sum Nov 1-18.

    Quarter To Date would be all of October plus Nov 1-18.

    And Year to Date would be July 1 (Fiscal Calendar) to November 18th.

    How can i do this without creating my own "To Date" functions


    Thursday, January 02, 2014 8:26 PM


All replies

  • DAX has some built in time intelligence functions like TOTALQTD & TOTALMTD. Is this what you were looking for?

    Paras Doshi (Blog: | Twitter: @Paras_Doshi )

    Wednesday, January 08, 2014 5:32 PM
  • in addition to what Paras Doshi already said there are of course also built-in functions to handle YTD calculations like TOTALYTD():


    as you can see it excepts an optional parameter <year_end_date> which can be used to handle fiscal years


    - -

    Thursday, January 09, 2014 8:07 AM
  • Hi Gerhard,

    How will the fiscal year logic work for TOTALQTD() ,there are no parameters that one can use to specify the fiscal year in the built-in time intelligence functions. Quarter 1 being April'2013-Jun'2013 and likewise.


    Monday, January 13, 2014 9:32 AM
  • how would your Calendar-QTD value vary from your Fiscal-QTD value in this case?

    should be the same value - right?

    QTD is independent of fiscal years unless you have a fiscal year that starts in the middle of a calendar quarter which I have never experienced at any of my customers yet


    - -

    Monday, January 13, 2014 4:29 PM