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.

     

naelske_cronos's avatar
naelske_cronos
Advocate II
7 years ago

Hello MattAllington 

 

I guess you understand me completely wrong or it is just me whom asked the question wrong. There's nothing wrong about my first DAX measure (M) 12 Rolling Months. CurrentDate is 30th of July 2019. The VAR AllDates is 30th of July 2019 minus 12 months so 31th of July 2018. This is right. Even cumulative is right.

 

I want to create another measure based on this measure but for the sameperiod for last year so instead of 31th of July 2018 till 30th of July 2019, I want to calculate from 31th of July 2017 till 30th of July 2018 with SAMEPERIODLASTYEAR.

 

As you can see in the table in the first image, it shows the almost right result but I know with SAMEPERIODLASTYEAR it shows the same period for last year on the same row. For example you see for july 2018 the sum 88.359,20 euros but on the same line next to it, it has to show 172.740,07 and that is the sum from july 2017.

 

 

Kind regards

 

MattAllington's avatar
MattAllington
Community Champion
7 years ago

Yes, you are right - I don’t understand what you are doing. Maybe partly because I can’t read the headings in your table. 

 

First column is year, second is month. I assumed the third is something additive, like sales. The 4th column says it is a 12 month rolling total, but that is not what it is showing. It is showing the same number as column 3. Maybe this is what you want - I don’t know. But it is not a rolling 12 month total if it shows the total for the current month for each row of the table. A rolling 12 month total will show the previous 12 month total regardless of what time period you select - that’s what the “rolling” part means. 

 

If you want the last 12 months sales, then that’s is something different.