Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

TotalMTD not pulling from this month

Hi everyone,

 

I'm hoping you can help me with a problem I'm having with my TotalMTD Dax.

 

Here is my formula for the measure: 

MTD Total Surgeries = TOTALMTD(sum(Surgery[Completed Surgeries]),Surgery[Date.Date])
 
I am trying to measure how many surgeries happened this month so far by service line (Orthopedics, Vascular, etc). 
 
I noticed that when a service line hasn't had a surgery this month yet, the measure defaults to count all surgeries from the last month when they did have a surgery. This means that my total MTD is less than the sum of my individual service MTDs. 
 
For example, my last opthalmology surgery occurred in August, and there were 8 total opthalmology surgeries that month. Since none have occurred yet this month in September, I want my MTD to be 0. However, my measure calcuates 8.
 
Can anyone help? I'd really apprecaite it!
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous,

     

    To use Time intelligence function, Continuous time is necessary for most time. you can try to generate a calendar table and using in your DAX.

     

    Calendar = CALENDARAUTO()
    MTD Total Surgeries = TOTALMTD(SUM(Surgery[Completed Surgeries]),'Calendar'[Date])

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Based on my test, Your formula works fine on my side, Could you please figure out what difference between my mockup data and yours?

     

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      https://www.dropbox.com/s/pdoi27zrvsq967d/TotalMTD-not-pulling-from-this-month%20v2.pbix?dl=0

       

      v-lid-msft 

      Thanks for your help! You're correct that there is no error in the table. I added a service column in the pbix above and showed how the error manifests in a bar graph visual. While there were no orthopedic surgeries in August, you'll notice that the bar graph shows 1 (which matches the july number).

       

      I think then the way to fix it may be to create a new column indicating when we are in the current month, and excluding any other data from the visual that way.

       

      Thanks for helping me think through this!

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous,

         

        To use Time intelligence function, Continuous time is necessary for most time. you can try to generate a calendar table and using in your DAX.

         

        Calendar = CALENDARAUTO()
        MTD Total Surgeries = TOTALMTD(SUM(Surgery[Completed Surgeries]),'Calendar'[Date])

         

         

         

        BTW, pbix as attached.

         

        Best regards,

        Community Support Team _ Dong Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.