Difference between two measures

[sara_pinto15]

Hello. I hope you are fine 🙂

I have two measures, one to know the SPLM values and the other to know the SPLY values, in the same period of time.
I want the values up to (Today - 1 day - 1 month) for SPLM and (Today - 1 day - 1 year) for SPLY.
The measurements are exactly the same, except for the time difference. Why does SPLY assume a different period of days and fetch the total for the month?

.SPLM =

Var LastValue =

CALCULATE(

    LASTDATE('FAT_Values'[CALDAY]),

    ALL('FAT_Values')

)

VAR LimitDate = EDATE(LastValue,-1)

RETURN

CALCULATE(

    [.Values_MTD],

    DATEADD('dCalendar'[Date],-1,MONTH),

    FILTER(

        ALL('dCalendar'),

        'dCalendar'[Date]<=LimitDate))

.SPLM =

Var LastValue =

CALCULATE(

    LASTDATE('FAT_Values'[CALDAY]),

    ALL('FAT_Values')

)

VAR LimitDate = EDATE(LastValue,-1)

RETURN

CALCULATE(

    [.Values_MTD],

    DATEADD('dCalendar'[Date],-1,YEAR),

    FILTER(

        ALL('dCalendar'),

        'dCalendar'[Date]<=LimitDate))

[v-zhangti · Accepted solution]

Hi, @sara_pinto15 

The two measures you provided are almost identical, except for the time period they cover. The SPLM measure calculates the values up to (Today - 1 day - 1 month), while the SPLY measure calculates the values up to (Today - 1 day - 1 year).

The difference between the two measures is in the DATEADD function used in the CALCULATE function. The DATEADD function is used to subtract a certain number of days, months, or years from a given date. In the SPLM measure, the DATEADD function subtracts one month from the current date, while in the SPLY measure, it subtracts one year from the current date.

Here is the modified SPLM measure that calculates the values up to (Today - 1 day - 1 month):

.SPLM =
VAR LastValue =
    CALCULATE(
        LASTDATE('FAT_Values'[CALDAY]),
        ALL('FAT_Values')
    )
VAR LimitDate = DATEADD(TODAY(),-1,MONTH)

RETURN
    CALCULATE(
        [.Values_MTD],
        FILTER(
            ALL('dCalendar'),
            'dCalendar'[Date]<=LimitDate
        )
    )

And here is the modified SPLY measure that calculates the values up to (Today - 1 day - 1 year):

.SPLY =
VAR LastValue =
    CALCULATE(
        LASTDATE('FAT_Values'[CALDAY]),
        ALL('FAT_Values')
    )
VAR LimitDate = DATEADD(TODAY(),-1,YEAR)

RETURN
    CALCULATE(
        [.Values_MTD],
        FILTER(
            ALL('dCalendar'),
            'dCalendar'[Date]<=LimitDate
        )
    )

Note that the DATEADD function subtracts one month or one year from the current date, and the TODAY function returns the current date.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.