Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate 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.

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity 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!!!