Last month to date is incorrectly giving the whole previous month

[vpatel55]

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

[shaunb94]

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.

[vpatel55]

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.

[shaunb94]

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

[vpatel55 · Accepted solution]

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.