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 DAX measure to calculate the rolling months ending with the current date. I also work with a cumulative based on what the user clicks if he/she wants to calculate with or without cumulative.

This is the same measure but for the sameperiod but for last year. In my table above you see that it shows for the same period for last year but not on the same line.

How am I getting this on the same line?

 

Kind regards

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.

     

8 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much!

      I work with a fiscal calendar so date functions don't work well.

      I was using SamePeriodLastYear(dateadd(date,1,day)) and it was giving me the correct daily results but wouldn't total correctly.

      This solution works!  THANK YOU!!!

  • 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

      • 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