Trying to understand SAMEPERIODLASTYEAR

[User7664]

As I understand it the SAMEPERIODLASTYEAR can be used in conjunction with CALCULATE(SUM( to return the sum of the amount in the same period last year.

Testing this I have a table with two columns

1. Date column with 2 years of dates

2. Amount column. With random numbers between 10 and 50.

I created a measure:

Measure = CALCULATE(SUM(Sheet1[Amount]),SAMEPERIODLASTYEAR(Sheet1[Date]))

I added a matrix table to the sheet and added the dates as the rows and the amounts and the measure as the values. I left the dates in a hierarchy to show years and quarters. I expected to see the amount on all quarters from Year 1 and Year 2 and the measure to only show an amount on quarter 1-4 on year 2.

The result in this view is that there are no amounts in the 2nd year for the measure.

I added another table to show the individual dates and this worked...

Does PowerBI not allow the measures to work in a hierarchy?

Whats the best way to be able to roll up the last years results in the hierarchy so that a user can flick between days, weeks, months, quarters and see the same result for the prior year?

https://postimg.cc/BLPBY8sX 

[parry2k · Accepted solution]

@User7664 you should add a date dimension in your model for any time intelligence calculation. There are many posts on how to add this dimension table, you can check my post here, and in your measure refer to the date column from this new date table/dimension rather than from sheet1

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡