SUM for same period last year , WITHOUT SAMEPERIODLASTYEAR()

[Rory1]

Hi everyone,

Hope someone can help me and I hope it is possible to get this done with a measure.. should be anyway.

The idea is to calculate a sum for the previous year for max available period in the current year (months, now it's Jan-Jul). Here is my calculation for the previous period, but in this case, I get data for the total previous year instead of SUM for Jan-Jul of the previous year (the period January-July is conditional and changes depending on the available data)

Prev year PACKS = VAR LastYear = MAX (Media_base[Year]) - 1 RETURN CALCULATE (SUM('Media_base'[Packs]), 'Media_base'[Year]= LastYear)

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @Rory1 ,

According to your description, I create a sample.

Based on the code you provided, it did get all the sum value of previous year, as the formula does not specifically specify the month, as long as the year is last year, it meets the requirements of the formula.

I modify the formula to:

Prev year PACKS =
VAR LastYear =
    MAX ( Media_base[Year] ) - 1
VAR MaxMonth =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Media_base' ),
            'Media_base'[Year] = MAX ( 'Media_base'[Year] )
        ),
        'Media_base'[Month]
    )
RETURN
    CALCULATE (
        SUM ( 'Media_base'[Packs] ),
        'Media_base'[Year] = LastYear,
        'Media_base'[Month] <= MaxMonth
    )

Then get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[lbendlin]

That is working as designed.  By the way, SAMEPERIODLASTYEAR is syntax sugar for DATEADD(...,-12,MONTH)

What you are after is YoY To Date.  That requires the combination of SAMEPERIODLASTYEAR  with another filter that indicates if the data value is "in the past"  for the previous year.  That's something you need to add as a column to your Calendar table.