Forum Discussion

jbrinker's avatar
jbrinker
Frequent Visitor
8 years ago
Solved

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.    Bookin...
Need Help
  • jthomson's avatar
    jthomson
    8 years ago
    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

SingSong's avatar
SingSong
Frequent Visitor
7 years ago

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's avatar
    jpt1228
    Icon for Responsive Resident rankResponsive Resident
    6 years ago

    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)