cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrinker Frequent Visitor
Frequent Visitor

Troubleshooting TotalMTD showing Blank

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.

calendar table.png

 

 

 

 

 

 

 

This is the relevant data from the SalesOrders table.

sales table.png

 

 

 

 

 

 

 

 

 

This is the relationship between the Calendar and SalesOrders tables.

relationships.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Troubleshooting TotalMTD showing Blank


@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

7 REPLIES 7
v-huizhn-msft Super Contributor
Super Contributor

Re: Troubleshooting TotalMTD showing Blank

Hi @jbrinker,

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

1.PNG6.PNG

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])

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

Best Regards,
Angelia 

jbrinker Frequent Visitor
Frequent Visitor

Re: Troubleshooting TotalMTD showing Blank

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

Super User
Super User

Re: Troubleshooting TotalMTD showing Blank


@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

Highlighted

Re: Troubleshooting TotalMTD showing Blank

Are you using CALENDARAUTO() ?

 

You could create a dynamic calendar that goes until today:

 

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

Re: Troubleshooting TotalMTD showing Blank

Or if you wanted until end of current month:

 

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

Re: Troubleshooting TotalMTD showing Blank

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

vacacelaivan Frequent Visitor
Frequent Visitor

Re: Troubleshooting TotalMTD showing Blank

@SteveCampbell Genius! thanks