none
DATESMTD and TOTALMTD returning the latest month in the table, not the current MTD

    General discussion

  • I am novice with Power Pivot. Trying to report a number of insurance policies quoted in current MTD. For a particular market segment, our latest quotes were dated in May (now is October), so I would expect to see 0 (zero) as current MTD total. However, the result shows the May totals (the latest available monthly data). 

    How can I see the current MTD total?

    I tried 2 different ways:

    =totalmtd(count(query[quote_date]),query[quote_date])

    =calculate(count(Query[QUOTE_DATE]),DATESMTD(Query[QUOTE_DATE]),ALL(query[quote_date]))


    • Edited by SashaCHI Tuesday, October 29, 2013 2:23 PM
    Tuesday, October 29, 2013 2:22 PM

All replies

  • do you have a separate time-table?

    It seems that you are referring to a date-column within your fact-table: 'query'[quote_date]

    its best practice to always have a separate time-table without any gaps which is then marked as date-table in PowerPivot to make all time intelligence-functions work correctly

    of course you also have to select a day or a month in order to make both MTD-functions return valid results

    hth,
    gerhard


    - www.pmOne.com -

    Tuesday, October 29, 2013 8:25 PM
    Answerer
  • Thanks, Gerhard.

    Yes, I was using the date-column within my fact table.

    1) I have multiple date fields on which I want to report MTD. Does this mean that I need multiple relations to the time-table?

    2) Where do I select day and month? In the statement,as an argument?

    Tuesday, October 29, 2013 10:02 PM
  • @1: yes, if you want to report on multiple date-columns using time-intelligence functions you either need multiple time-tables or multiple relationships (active and inactive)

    @2: well, you somehow need to select a day for which you want to calculated the MTD value
     this is usually done in the pivot or report-filter
    though, you may also put your single days on rows in your pivot-table


    - www.pmOne.com -

    Wednesday, October 30, 2013 9:18 AM
    Answerer