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

    Question

  • 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

    Thx

    Thursday, January 02, 2014 8:26 PM

Answers

All replies

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

    http://technet.microsoft.com/en-us/library/ee634763.aspx


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

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

    TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

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

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, January 09, 2014 8:07 AM
    Answerer
  • 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.

    Thanks.

    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

    -gerhard


    - www.pmOne.com -


    Monday, January 13, 2014 4:29 PM
    Answerer