MTD and LYMTD - does not show correct values

[LisaB]

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). 

    TotalMTD = TOTALMTD(SUM(PostedSalesInvoices[Amount]);dCalendar[Date])

2. I also want a LY MTD but this shows me the sales for the whole month previous year, not MTD.

   Totalt LMTD = CALCULATE ( [TotalMTD]; SAMEPERIODLASTYEAR( dCalendar[Date] ) )

Thanks!

Lisa

UPDATE: It might be worth to mention that YTD works fine.

[Anonymous]

@LisaB,

How do you create the visual using the above measures. Your DAX works as expected in my Desktop.


Regards,
Lydia

[LisaB]

Hi @Anonymous,

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.

[MattAllington · Accepted 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

DateHasPassed = 'Calendar'[Date] < TODAY()

then filter on TRUE on your page

This should fix it.

[fahadnazir]

@MattAllington  Bravo man, i spent 2 hours finding a solution to this !!!!

[LisaB]

Thank you Matt!

[Greg_Deckler]

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...

[LisaB]

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