MTD Measure not working

Yeriel · June 21, 2020, 12:13pm

Hello guys.

I know this is a simple time measure and I’ve used it in other PBIX files but for some reason, I cannot make it work. I’ve tried using three different measure and nothing:

CALCULATE( [Total Sales], DATESMTD( Dates[Date] ) )
TOTALMTD( [Total Sales], Dates[Date] )
CALCULATE( [Total Sales], DATEADD( Dates[Date], 0, MONTH )

On the other hand, if I change the measure to YTD, but it doesn’t work for QTY or MTD.

If someone can guide me I will appreciate it.

I attached the PBIX file.

Cheers.Example Data (1).pbix (284.0 KB)

Harsh · June 21, 2020, 2:44pm

Hello @Yeriel,

Thank you for posting your query onto the Forum.

This problem is causing due to the “Date” table i.e. in the “Fact Table” you have the data till the month of June - 2020 and the “Date” table has the dates till 31st December, 2020.

Therefore, if you select “2019” from the year slicer you’ll see the values of MTD instead of “Blank”. The reason for this is there is complete data for that particular duration. Below is the screenshot provided for the reference.

If you select any of the month between “Jan to June” from month slicer and select year as “2020” still you’ll be able to see the values of MTD instead of “Blank”. Below is the screenshot provided for the reference.

Now lastly, if you don’t select any of the slicer then it will show “Blank” because the logic applied over here is that you have the last date in your “Date” table as 31st December, 2020 and since there is no data available for that period or after the month of June - 2020 it is showing you as “Blank”. Below is the screenshot provided for the reference.

The Solution to this is, in the “Date” table “IsAfterToday” column is hidden, unhide that column and bring it to the section “Visual Filers on this Page” and select/check the “FALSE” option and now the months under the slicer “Short Month Name” will show only those months which are active and therefore you’ll not face the problem where MTD used to be shown as “BLANK”.

Below is the screenshot provided for the reference.

In the above screenshot, now you’ll see that I haven’t selected any of the slicer and still we can the latest figure of MTD.

I’m also attaching the PBIX file for the reference.

Hoping you find this useful and helps you in your analysis.

Please feel free to write back in case I’ve missed out on anything.

Please Note: There is also a series of video made for this type of query which was created by @Melissa and @BrianJ which I recommend you to watch.

Thanks & Warm Regards,
Harsh

Example Data (1).pbix (334.3 KB)