Forum Discussion
SamePeriodLastYear - MTD Issue
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.
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
1 Reply
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