SamePeriodLastYear - MTD Issue

[isa2003]

Hello:

I am learning how to use the time difference functions.
I have a date column in my Fact table that is connected to a Date table.
I would like to calculate YTD, QTD, MTD, Week to date sums of the Amount column.

I used the filters in the report and the calculations are correct. However, I run into issues with capturing the same using measures. For instance, today is 6/6/24. For the MTD, the calculation is correct. However, the corresponding sum for the same period from last year gives me the total June sum (not the sum from June 1-June 6).

Previous Year MTD = 
CALCULATE(
    [Amount],
    DATESMTD(SAMEPERIODLASTYEAR('DateT'[Date]))
)

Any guidance would be much appreciated. Thanks in advance.

[v-shex-msft · Accepted solution]

HI @isa2003 ,

Perhaps you can try to manually calculate the filter range based on date function instead of nested using time intelligence functions:

Time Intelligence "The Hard Way" (TITHW)  

Previous Year MTD =
VAR currDate =
    MAX ( 'DateT'[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
RETURN
    CALCULATE (
        [Amount],
        FILTER (
            ALLSELECTED ( 'fact' ),
            [Date] >= DATE ( YEAR ( prevDate ), MONTH ( prevDate ), 1 )
                && [Date] <= prevDate
        )
    )

Regards,

Xiaoxin Sheng