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

SteveCampbell's avatar
SteveCampbell
Icon for Memorable Member rankMemorable Member
8 years ago

Are you using CALENDARAUTO() ?

 

You could create a dynamic calendar that goes until today:

 

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