Forum Discussion

naelske_cronos's avatar
naelske_cronos
Advocate II
7 years ago
Solved

SAMEPERIODLASTYEAR - wrong result

Hello,   I know that when you use the function SAMEPERIODLASTYEAR(), you can compare the current period like in my picture with the sameperiod but for last year on the same row.  This is my ...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    Hi, naelske_cronos 


    I tried to understand what you mean and provide a solution. I used DATEADD() function to get the value of the same period last year.

     

    Create a column to convert Year and Month to a date type value.

     

    dateFormat =
test[Month] & "-" & test[Year]

     

    After creating it, select the data type option to choose DATE type

     

    Then edit relationship between your calendar table and your data table.

     

     

    Then create the following measure:

    rolling 12 Month PY =
VAR rollingMonths =
    CALCULATE ( SUM ( test[Sales] ), DATEADD ( 'Calendar'[Date], -12, MONTH ) )
RETURN
    rollingMonths

    In the report, you need to choose these fields:

     

    In the Date fields, you just need Year and Month.

    Now, you can get the visual you want.

    Best Regards,

    Eads

     

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

     

MattAllington's avatar
MattAllington
Community Champion
7 years ago

Your currentDate Variable is not correct. You should use =MAX(Calendar[date])

 

the objective is to find the last date for each row in the table, with the current table filters applied. You are taking today’s date - which is  a completely different thing

  • MattAllington's avatar
    MattAllington
    Community Champion
    7 years ago

    Did you try my suggestion?

    • naelske_cronos's avatar
      naelske_cronos
      Advocate II
      7 years ago

      MattAllington 

       

      I did try the solution but I don't know how it helps me to get the measure 12 Rolling Months PY for the SAMEPERIODLASTYEAR on the same line as 12 Rolling Months? The first measure works because it shows non-cumulative as cumulative so that's no problem.

       

      Kind regards

      • MattAllington's avatar
        MattAllington
        Community Champion
        7 years ago

        Here is a generic formula for 12 months rolling total

         

        =
        VAR myLastDate =
        MAX ( calendar[date] )
        RETURN
        CALCULATE (
        [total sales],
        DATESINPERIOD ( calendar[date], myLastDate, -1, YEAR )
        )