CODEWITHSUNDEEP
powerbi
jules
jules

Reputation: 1

power bi calculate sameperiodlastyear problem

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?

Upvotes: 0

Views: 8351

Answers (2)

Rolando Isidoro
Rolando Isidoro

Reputation: 5114

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:

enter image description here

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

enter image description here

Hope it helps.

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

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

Output

Upvotes: 2

Related Questions