MTD vs LY MTD not working

DAX DAX Calculations
1

I am using the Mastering Dax Calculations. Having issue creating the LY MTD (Sameperiod)

Sales MTD = TOTALMTD(sum(Sales[Total Revenue]), Dates[Date]) // this is working great

MTD LY = CALCULATE([Sales MTD], SAMEPERIODLASTYEAR(Dates[Date]))

MTD LY is not getting correct results.

What I am doing wrong.

2

@BI_Analyst Try something like this:

MTD Last Year =
VAR MaxDate =
    CALCULATE (
        MAX ( Dates[Date] ),
        SAMEPERIODLASTYEAR ( Dates[Date] )
    )
VAR Result =
    CALCULATE (
        CALCULATE (
            [Total Sales],
            Dates[Date] <= MaxDate,
            VALUES ( Dates[Month] )
        ),
        SAMEPERIODLASTYEAR ( Dates[Date] )
    )
RETURN
    Result
3

I am not getting the results for the last 1-27 Jan 2020 with the code.

Code below show for Jan 2020 whole month instead of first 27 days of Jan 2020.

Sales MTD LY =
CALCULATE(TOTALMTD(sum(Sales[Total Revenue]), Dates[Date]),
SAMEPERIODLASTYEAR(Dates[Date]))

Any idea how to fix to show only the days parallel in last year month to what we have MTD in Current year.

4

This is what my code does.

5

ok, so when I checked the code is showing 31 days of Jan 2020 total of 5 M. It should be up to 27th of Jan 2020 total of 4.3 mil.

6

@BI_Analyst Create a new column in Date table:

Past Dates = Dates[Date] <= Today()

It will return TRUE/FALSE

Use that in outer CALCULATE as Dates[Past Dates] = TRUE ()

7

Hi @BI_Analyst, did the response provided by @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

8

Hi @BI_Analyst, we’ve noticed that no response has been received from you since the 28th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!