locked
Measure returning blank RRS feed

  • Question

  • The following measure returns Month Sales: (blank)

     Month Sales:=TotalMTD(Sum([Sales Amount]),'Date'[Full Date])

    I have a relationship between ResellerSales-OrderDateKey and Date-DateKey

    The relationship works fine when I use it in Access 

    Thursday, June 25, 2015 7:56 PM

Answers

  • A function like TOTALMTD and TOTALYTD often returns blank when in the Calculation Area, because of what these functions do: perform a calculation between the last date in the current context and the first day of the month (or year). In the Calculation Area, typically the date table is not filtered so the last date is the last date in your date table. In many cases the first day of the month or year is after the last day of data in your model.
    • Proposed as answer by Charlie Liao Wednesday, July 8, 2015 1:06 AM
    • Marked as answer by Michael Amadi Thursday, August 27, 2015 12:48 PM
    Monday, June 29, 2015 1:58 PM
    Answerer

All replies

  • In what context is it returning (blank)? Is this in the designer window? If so have you tried creating a pivot table with the [Full Date] on the rows and this measure on the columns?

    http://darren.gosbell.com - please mark correct answers

    Thursday, June 25, 2015 9:19 PM
  • If I create a pivot table the measure shows up ok.
    Friday, June 26, 2015 8:10 PM
  • A function like TOTALMTD and TOTALYTD often returns blank when in the Calculation Area, because of what these functions do: perform a calculation between the last date in the current context and the first day of the month (or year). In the Calculation Area, typically the date table is not filtered so the last date is the last date in your date table. In many cases the first day of the month or year is after the last day of data in your model.
    • Proposed as answer by Charlie Liao Wednesday, July 8, 2015 1:06 AM
    • Marked as answer by Michael Amadi Thursday, August 27, 2015 12:48 PM
    Monday, June 29, 2015 1:58 PM
    Answerer