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

jbrinker's avatar
jbrinker
Frequent Visitor
8 years ago

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'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() 
             )

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

    Or if you wanted until end of current month:

     

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