locked
DatesMTD Function and no row data RRS feed

  • Question

  • I have a DATESMTD function which is not working.  This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero.  See below my formula:

    MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])

    Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.

    So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date  for Sept instead of zero.  What can I do to make the system insert zero in the month to date column instead of the $200.00.  What am I doing wrong in the formula

    Help


    Kofi

    Tuesday, October 20, 2015 8:13 PM

Answers

  • The simplest should be the following:

    MTDSum:=
    TOTALMTD(
        SUM('Combined Years Dataset'[Net])
        ,'Date'[date]
    )

    This is assuming that your 'Date' conforms to the requirements I laid out above. The measure above is *exactly* the same as the following:

    CALCULATE(
        SUM('Combined Years Dataset'[Net])
        ,DATESMTD('Date'[date])
    )



    GNet Group BI Consultant

    • Marked as answer by kdankwah Monday, October 26, 2015 1:46 AM
    Tuesday, October 20, 2015 10:02 PM
  • Kofi, I am unable to reproduce the effect you are seeing. When I create the measure I provided above with sample data, I see no pivot table row label for months with no fact rows.

    Ensure that you are using row labels from the date dimension in the pivot and not from the fact table, and make sure you have marked your date dimension as the date table in the Power Pivot model. If you continue to see inappropriate behavior, please share a workbook that has sample data and recreates your issue.

    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Monday, October 26, 2015 1:37 AM
    • Marked as answer by kdankwah Monday, October 26, 2015 1:46 AM
    Wednesday, October 21, 2015 11:24 PM

All replies

  • I am using Excel 2010 with PowerPivot.

    Thanks


    Kofi

    Tuesday, October 20, 2015 8:29 PM
  • If you insist on writing unreadable single line measures, that's fine, but please share formatted measures with the rest of us, using DAX Formatter.

    MTDSUM :=
    CALCULATE (
        SUM ( 'Combined Years Dataset'[Net] ),
        DATESMTD ( 'Combined Years Dataset'[Period2] ),
        'Date'[date]
    )
    

    DATESMTD() takes a column of dates. For it to work as expected, you should pass it a reference to your date dimension date field, and that field should contain a contiguous set of dates that span from January 1 in the first year you have data to December 31 in the last year you have data. If it is not referring to this field, you will get unexpected behavior.

    Second item of note, why do you have a reference to your date dimension as an argument to CALCULATE()?

    GNet Group BI Consultant

    Tuesday, October 20, 2015 8:57 PM
  • So sorry for the un formatted measure.  I am a newbie and I have noted your frustration and corrected the formula, on the second note,  I was calculating the sum based on the dates.  Can you please tell me the right way to do this?

    Thanks


    Kofi

    Tuesday, October 20, 2015 9:16 PM
  • The simplest should be the following:

    MTDSum:=
    TOTALMTD(
        SUM('Combined Years Dataset'[Net])
        ,'Date'[date]
    )

    This is assuming that your 'Date' conforms to the requirements I laid out above. The measure above is *exactly* the same as the following:

    CALCULATE(
        SUM('Combined Years Dataset'[Net])
        ,DATESMTD('Date'[date])
    )



    GNet Group BI Consultant

    • Marked as answer by kdankwah Monday, October 26, 2015 1:46 AM
    Tuesday, October 20, 2015 10:02 PM
  • Thanks so so much, Greg.  I will try and report back.  You are a life saver.

    Best,

    Kofi


    Kofi

    Wednesday, October 21, 2015 1:02 AM
  • The formula above worked alright, but I still get this problem, when a department has no data for the month, it insert the ytd figures to the month to date column.  To test it, I inserted a row with a zero value to the department with no data for current month and it worked perfectly with a zero in the month to date column. 

    So in other words the function is looking for data in the current month to apply to the month to date column and if there is none it inserts what is in the YTD.

    Thanks


    Kofi


    • Edited by kdankwah Wednesday, October 21, 2015 8:46 PM
    Wednesday, October 21, 2015 8:36 PM
  • Kofi, I am unable to reproduce the effect you are seeing. When I create the measure I provided above with sample data, I see no pivot table row label for months with no fact rows.

    Ensure that you are using row labels from the date dimension in the pivot and not from the fact table, and make sure you have marked your date dimension as the date table in the Power Pivot model. If you continue to see inappropriate behavior, please share a workbook that has sample data and recreates your issue.

    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Monday, October 26, 2015 1:37 AM
    • Marked as answer by kdankwah Monday, October 26, 2015 1:46 AM
    Wednesday, October 21, 2015 11:24 PM