power bi calculate sameperiodlastyear problem

Question

I have a table of data for two years where I want to retrieve the previous years value for a date. my table the same date shown many times delineated by another value e.g.

Employee, Date, Sales, Prev yr sales

Sam, 1/07/2017, 100

Sam, 2/07/2017, 120

John, 1/07/2017, 90

John, 2/07/2017, 23

etc

Sam, 1/07/2018, 200, 100

Sam, 2/07/2018, 21, 120

John, 1/07/2018, 45, 90

John, 2/07/2018, 130, 23

etc

I am using a dates table created in DAX and have made a relationship between the dates in my table and the dates table.

I have created a measure for the sales & a column for the previous year sales. the latter using calculate and the sameperiodlastyear:

Prev Yr Sales = CALCULATE([Sum Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))

my problem is I cant get the prev yrs sales value out in a table if there are multiple rows per date i.e. sam and john. if there is only one employee the function works.

Can anyone help?

Answer 1

When you are writing a calculated column with a CALCULATE in it, the row context from the table becomes the filter context you are working evaluating in. For this calculation, you don't want any of that row context except for the employee name, so you can remove it using the ALLEXCEPT() function:

Prev Yr Sales =
CALCULATE(
    [Sum Sales],
    ALLEXCEPT(Sales, Sales[Employee]),
    SAMEPERIODLASTYEAR('Calendar'[Date])
)

Answer 2

I've done a quick simulation of the case you've described and found out that the reason you're not getting the expected results might be missing .[Date] on the end of your SAMEPERIODLASTYEAR function argument.

Check out the Data view on my simulation and the highlighted adjustment in the Prev Yr Sales measure:

And the matching result on a Matrix visualization on the Report view:

Hope it helps.