SAMEPERIODLASTYEAR not giving correct results

[TT_newbie]

Hi,

I'm having some troubles with my SAMEPERIODLASTYEAR calculations.

I'm trying to compare the first 3 days of March '26 against the same period for last year using SAMEPERIODLASTYEAR, but what I get is the total amount of March '25.

The formula I'm using is:

Sales Y-1 = CALCULATE(

SUM( SalesAnalysis[Sales Amount] ),

SAMEPERIODLASTYEAR( Calendar[Date] )

)

I created the Calendar table and set it as Date table

Also, I've already tried adding ALL( Calendar[Date]) since I'm filtering the visual using a slicer with Calendar[Date] as data field; but that didn't work.

SalesAnalysis table is linked to the Calendar by SalesDate column

Thanks in advance for your help!

[TT_newbie]

Dears,

thank you all for your answer!

they made me realize that the mismatch was between the last day of Calendar Table (set using TODAY() ) and the last SalesDate available (today too since the report is daily refreshed). To me, adding one day to the calendar (TODAY()+1) did the trick, but I believe the DATEADD solutions is cleaner.

For explanation purposes, the filter I was using had day granularity

[danextian]

Hi @TT_newbie 

That is the expected behaviour of SAMEPERIODLASTYEAR - it returns the whole year unless you add the day of month as a filter.

Value SPLY 2 = 
VAR LatestDateWithValue =
    CALCULATE (
        LASTNONBLANK ( Dates[Date], [Sum of Value] ),
        ALLSELECTED ( Dates[Date] )
    ) -- or TODAY()
VAR _MMDD =
    FORMAT ( LatestDateWithValue, "MMDD" )
RETURN
    CALCULATE (
        [Sum of Value],
        SAMEPERIODLASTYEAR ( Dates[Date] ),
        KEEPFILTERS ( FORMAT ( Dates[Date], "MMDD" ) <= _MMDD )
    )

Please see the attached pbix.

[Ashish_Mathur]

Hi,

Your formula is correct.  Do one of the following:

1. Ensure that your Calendar goes only until today; or
2. Create a Date slicer from the Calendar table and select the first 3 days.

[Murtaza_Ghafoor]

@TT_newbie 
Why you are getting full month instead of three days.

The issue is that your slicer is selecting the full month, so:

SAMEPERIODLASTYEAR shifts the full month.

To compare only first 3 days:

Use a date range slicer, add dates in there.

Proposed Solution:

Make sure your slicer filters exact dates (not full month).

Use this DAX formula instead of sameperiodlastyear, this is easy to debug.

Sales Y-1 =

CALCULATE (

    SUM (SalesAnalysis [Sales Amount]),

    DATEADD (Calendar [Date], -1, YEAR)

)

If this helps, ✓ Mark as Kudos | Mark as Solution| Help Others

[collinq · Accepted solution]

HI  ,

According to the DAX page, SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Learn for Sameperiodlastyear, "For calendar inpu...

Sales Y-1 (Selected Range) :=

VAR MinDate = MIN ( Calendar[Date] )

VAR MaxDate = MAX ( Calendar[Date] )

RETURN

CALCULATE (

    SUM ( SalesAnalysis[Sales Amount] ),

    DATEADD (

        DATESBETWEEN ( Calendar[Date], MinDate, MaxDate ),

        -1,

        YEAR

    )

)

@TT_newbie