Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Have been searching the forum for a while but without finding any answers to my questions:
1. I have created a MTD measure but this won't show any values if I don't have a date filter (= this month).
Solved! Go to Solution.
Technically you are getting the correct results but I think what you are seeing is the date over run problem. Jan 2019 is only a few days old, so MTD is showing the correct answer, but your calendar table has dates through to the end of Jan 2019 (technically it is correct to load your calendar table with the full year). When MTD LY is calculated, it takes the current month (Jan 2019, which includes all days in Jan) and gives you the result for the same month prior year (Jan 2018). So you get the full month this year (but it is only a few days) and you get the full month last year.
OK, so how to "fix" it.
One way is to add a calculated column to your calendar table that you can use in your page filter. Something like this
@LisaB,
How do you create the visual using the above measures. Your DAX works as expected in my Desktop.
Regards,
Lydia
Hi @v-yuezhe-msft,
Thank you for your reply.
I would like it as in Picture 1, but with correct values. 🙂 I have a page filter on this month which gives me the correct value for January 2019.
Picture 2 shows how it looks like without any filters (unfortunately it's in Swedish).
Thanks.
Technically you are getting the correct results but I think what you are seeing is the date over run problem. Jan 2019 is only a few days old, so MTD is showing the correct answer, but your calendar table has dates through to the end of Jan 2019 (technically it is correct to load your calendar table with the full year). When MTD LY is calculated, it takes the current month (Jan 2019, which includes all days in Jan) and gives you the result for the same month prior year (Jan 2018). So you get the full month this year (but it is only a few days) and you get the full month last year.
OK, so how to "fix" it.
One way is to add a calculated column to your calendar table that you can use in your page filter. Something like this
Thank you Matt!
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Hi @Greg_Deckler,
Thank you your reply. Actually, I have dates on my transactions and a separate date table. Worth to mention is that the YTD works perfectly fine, it's only the MTD that messes things up.
Lisa