topic Re: Last month to date is incorrectly giving the whole previous month in DAX Commands and Tips

Last month to date is incorrectly giving the whole previous month

vpatel55 — Thu, 12 Nov 2020 17:50:29 GMT

My MTD measure correctly shows sales between 1st November to the 11th of November.

--> I'm looking to find out what the MTD was the previous month, in the same equivilent period (i.e. 1st October to the 11th of October).

However, it is returning the whole previous month. Here are measures that I have tried. They all give the same wrong answer (12k).

I have a calendar table (marked as a date table) and joined to the dates in my fact table.

Here are my three attempts:

Sales MTD LM (Attempt 1) = CALCULATE ( [Sales], DATEADD ( FILTER ( DATESMTD ( 'Calendar'[Date] ), 'Calendar'[Date] < TODAY () ), -1, MONTH ) ) Sales MTD LM (Attempt 2) = CALCULATE ( [Sales MTD], PARALLELPERIOD ( 'Calendar'[Date], -1, MONTH ) ) Sales MTD LM (Attempt 3) = CALCULATE ( [Sales MTD], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

There is a video by Curbal to tackle this very problem, and as far as I can tell, I have followed her measure exactly: https://www.youtube.com/watch?v=-xBYtOVyMTs&ab_channel=Curbal

Yet, all attempts are incorrect, giving the whole previous month:

Any help would be appreciated. The correct answer should be 4,449 (although if you try it later on, it will of course be higher). Here is the file, containing dummy data:

https://www.dropbox.com/s/vq8wgwnnl3yk0iv/Sample%20report%201.pbix?dl=0

Re: Last month to date is incorrectly giving the whole previous month

vpatel55 — Thu, 12 Nov 2020 19:19:48 GMT

I've found the answer, and adding it here in case someone comes across the same issue.

The trick is *not* to use the calendar table as the date table. If I use the dates in the fact table, it works fine:

Sales MTD LM (Attempt 4) = CALCULATE ( [Sales], DATEADD ( FILTER ( DATESMTD (Data[Date] ), Data[Date]< TODAY () ), -1, MONTH ) )

The reason why I think this is the case, is that the calendar table is marked as a date table, and so it applies an 'ALL' to these time intelligence calculations. So it did not matter what filter I used, it would always select ALL anyway.

Re: Last month to date is incorrectly giving the whole previous month

shaunb94 — Mon, 16 Jan 2023 18:05:33 GMT

Hey Vpatel, I know this thread is quite a while ago, but your solution does not seem to be working for me as it is still showing the full month rather then MTD. Any ideas what may be up? I have ensured to use the Fact Date table column rather than the Date table one.

Re: Last month to date is incorrectly giving the whole previous month

vpatel55 — Tue, 17 Jan 2023 19:09:40 GMT

hI shaunb94 I'm afraid I would really need to see the model to see what is going on. I'm busy over the next few days, so it may be worth posting a new question, using a the same model but using dummy data, and hopefully a member of the community can help if I can't.

Re: Last month to date is incorrectly giving the whole previous month

shaunb94 — Tue, 21 Mar 2023 17:52:35 GMT

hi vpatel55, I thought I came back here but I got it worked out in the end. Thanks for your help.