MTD shows blank when using Calendar

powerbideveloper · September 22, 2019, 7:24pm

I can get MTD and YTD to work when using my Sales Date column but it shows blank when I use the Date Calendar.

I am using the Date script that comes with the download files and it works fine except when I try and use MTD and YTD Measures. I use the exact code from the MTD video here.

Hope you can help, thank you!

JarrettM · September 23, 2019, 12:10am

It probably has to do with the relationship you have, or don’t have setup which is causing the error. In order to properly answer you question, please post a sample PBIX file that you have with these measures.

Thanks

powerbideveloper · September 23, 2019, 2:06am

Thanks for your reply, Ojones! I will put together a pbix with some data, will take a few hours to get to.

sam.mckay · September 23, 2019, 5:37am

The answer to this will be super easy. It will be some error with the relationships in your model or the context you are placing in the visual does not align with the formula that you have.

Even just adding a lot of images to your support request will assist in getting an answer much quicker.

Sam

powerbideveloper · September 23, 2019, 6:20am

Thanks Sam, yes it might be my understanding of how these work that is the problem. I have attached a pbix with a small sample. EntDNA_1.zip (139.6 KB)

The MTD and QTD seem to work when I add a month Slicer but how would I use it in a report that doesn’t require a visual that applies the month or quarter context?

Thank you!

JarrettM · September 23, 2019, 11:52am

Your relationship was setup correctly, just had to make an adjustment to your Measures. Also need to make sure that Year & MonthOfYear columns in your Date table are setup as whole number. Here are measures. Will also attach copy of PBIX I worked on.

Thanks
EntDNA_1.pbix (136.1 KB)

powerbideveloper · September 23, 2019, 4:29pm

Works like a charm Ojones, thanks so much! I’ve asked this question at different forums and you guys seem to know it best, so thankful for that!!

powerbideveloper · September 26, 2019, 11:21pm

Hi Ojones, did the MTY and QTD functions not work for you either, do you know why?

JarrettM · September 27, 2019, 11:29am

The way you originally had the setup di not work. Don’t remember why, but the one’s I sent over should work. I will need more details in a sample PBIX if you are running into issues.

Thanks

powerbideveloper · September 27, 2019, 2:32pm

No issues with your solution, works perfectly, just wondering why the MTD and QTD functions don’t work??