SAMEPERIODLASTYEAR - wrong result

[naelske_cronos]

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

[v-eachen-msft · Accepted solution]

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]

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]

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]

Did you try my suggestion?

[naelske_cronos]

@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]

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 )
)

[naelske_cronos]

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]

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.