Forum Discussion

isa2003's avatar
isa2003
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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