Troubleshooting TotalMTD showing Blank

[jbrinker]

I am having trouble doing what should be a very simple Measure using the TotalMTD formula.  Below is a Measure which sums sales order data and I am trying to show the month to date value. 

Bookings MTD = TOTALMTD(SUM(SalesOrders[ompFullOrderSubtotalBase]),'Calendar'[Date])

I have a table called Calendar which is used as my dates table.  This contains a continuous set of days in the Date column.

This is the relevant data from the SalesOrders table.

This is the relationship between the Calendar and SalesOrders tables.

I think it has something to do with the date field but cannot figure out the problem.  Doing that exact same measure using TOTALYTD and TOTALQTD works as expected. 

Any suggestions would be greatly appreciated.

[SingSong]

I have also recently run into the same problem, but the solution of restricting the calendar to today does not fit my needs as I also have to provide forecasting information for future dates from the same data model i.e. I have plan data for the future in there too.

The solution I came up with was to create an aditional calculated column on my calendar table with true or false for Beforetoday  Before Today= If('Calendar'[Date] < Today(),True,False)

I then filter the MTD calculation

Bookings MTD:= Calculate(TOTALMTD(SUM(SalesOrders[ompFullOrderSubtotalBase]),'Calendar'[Date]),FILTER('Calendar','Calendar'[Before Today]=True))

This seems to work well for my needs where future dates are required.

SingSong

[jpt1228]

Hello @SingSong  This was a simple and brilliant solution to my issue of custom fiscal period calculations for running totals. By using your suggestion of filtering the page on is before today "Y" all my custom time period calculations work for Fiscal - Year, Period, Week.

The solution I came up with was to create an aditional calculated column on my calendar table with true or false for Beforetoday  Before Today= If('Calendar'[Date] < Today(),True,False)

[v-huizhn-msft]

Hi @jbrinker,

Please change the filter to "both", and check if it works fine.



I use the function and get expected result.

Year-to-Day = TOTALYTD(SUM(Sales[SALE]),'Calendar'[DATE])
Year-to-month = CALCULATE(SUM(Sales[SALE]),DATESMTD('Calendar'[Date]))
Year-to-Quarter = TOTALQTD(SUM(Sales[SALE]),'Sales'[DATE])

expected result
Please download the attachment file(expected result page) to check more information.

Best Regards,
Angelia 

[jbrinker]

Hi @v-huizhn-msft,

Thanks for the proposed solution and sample files.  I did take a look at the files and tried your suggestion. Unfortunatly that did not solve the problem but did help me to do some further troubleshooting.

Your example file it pulls in the MTD data for July 2015 which is the last dates you have populated in your Calendar table.  Everything works as expected. 

My problem is that my Calendar table had dates populated for the full 2017 calendar year (through December).  The MTD calculation I believe uses the last month from the Calendar table which would be December.  Currently December has no Sales data therefore it is displaying Blank in the Card visual.  If I change the query on the Calendar table to only show to November 2017 everything works as expected but I don't want to have to update that each month.

How can I create a measure for MTD that uses the current month? 

Any suggestions are greatly appreciated.

Thanks,

Jeff

[SteveCampbell]

Are you using CALENDARAUTO() ?

You could create a dynamic calendar that goes until today:

date = CALCULATETABLE(
                  CALENDAR(Min(TABLE[DATE]),
                 TODAY() 
             )

[vacacelaivan]

@SteveCampbell Genius! thanks

[SteveCampbell]

Or if you wanted until end of current month:

Date =
CALCULATETABLE (
    CALENDAR (
        MIN ( Table[date] ),
         DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 )  - 1   )
     )

[jthomson · Accepted solution]

---

@jbrinker wrote:

My problem is that my Calendar table had dates populated for the full 2017 calendar year (through December).  The MTD calculation I believe uses the last month from the Calendar table which would be December.  Currently December has no Sales data therefore it is displaying Blank in the Card visual.  If I change the query on the Calendar table to only show to November 2017 everything works as expected but I don't want to have to update that each month.

---

Could you not change the query so it looks at the local date and removes dates from your calendar table that are greater than it (like in this post - https://community.powerbi.com/t5/Desktop/Filter-rows-in-query-editor-up-to-current-date/td-p/34124)? Would save you having to modify it monthly

[jbrinker]

Thanks for the suggestion as that worked great and solved the problem.