TotalMTD not pulling from this month

[738o51]

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!

[v-lid-msft]

Hi @738o51 ,

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.

[738o51]

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 · Accepted solution]

Hi @738o51,

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.

[738o51]

@v-lid-msft 

This worked! I was using a date from the surgery table. After creating the calendar table and using that date instead, things seem to be working. Thank you!