MTD Measure to current date

[Sachet_716]

Please i need some help. I am trying to create a MTD measure in my report.

I am extremely new with DAX and power bi. 

I was thinking that i was doing it correctly, however, i have some stores that are no longer active on November but stil shows MTD figure, i dont know why  😞

MTD Sales = TOTALMTD(sum(CVR[Functional Sale Value]) , CVR[Full Date]) have also tried using my DateDim table

MTD Sales = TOTALMTD(sum(CVR[Functional Sales Value]), DateDim[Date]). 

I am close to being so frustrated about this  😞 

Thank you in advance. 

[v-yuezhe-msft]

@Sachet_716,

Please share sample data of your table and post expected result in table format. You can upload sample data to OneDrive and post shared link here.

Regards,
Lydia

[Kieran]

Based on your requirements it looks like you have chose the correct function so you are heading in the right direction ... https://msdn.microsoft.com/en-us/library/ee634560.aspx.

As a next step please show the relationship within a screen shot of the relationship diagram between your CVR measure / fact table and your DateDim dimension.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

[Sachet_716]

Hello @Kieran, 

Here is my table. I am expecting that the 2 stores which i highlighted will appear since i need MTD data, without doing any filters. However, whenever i choose 'November' in my slicer, it gives me the correct data. 

I might be doing it wrong?

Thank you. 

Cris

[v-yuezhe-msft · Accepted solution]

@Sachet_716,

You would need to add Month field or Date field to your visual. For more details about how TOTALMTD function works, please review this article: https://msdn.microsoft.com/en-us/library/ee634560.aspx.






Regards,
Lydia

[Sachet_716]

Thank you Lydia.  🙂