Hi!
I am new to using PowerPivot and I am hoping someone could help me with the following question.
I need to create a measure that shows the total number of incidents logged for the same period last year. I have created a calendar table and I have also added a column named Week Commencing as my reporting week starts on a Friday.
If I use the following measure, the total is not correct for the previous year.
=CALCULATE(COUNT('Incident Data'[Number]),SAMEPERIODLASTYEAR('Calendar'[Date]))
If I use the below measure, the cells are blank.
=CALCULATE(COUNT('Incident Data'[Number]),SAMEPERIODLASTYEAR('Calendar'[Week Commencing]))
Any help would be appreciated.
Kind regards
Jeremy
Hi Jeremy,
Welcome to our forum! Thanks for attaching your file. If you build a PivotTable and place the Date field in the rows, then expand it down to the Day level of detail you can see the values that make up the 'Last Year' figures e.g.:
- The 'Last Year' value for Jan 4, 2019 of 441 consists of the values for Jan 5 through 10, 2018, and
- The 'Last Year' value for Jan 11, 2019 of 481 consists of the values for Jan 11 through 17, 2018.
In other words, there was no Jan 4 value for 2018, therefore the 'Last Year' value for Jan 4, 2019 only includes values for 6 days. When working with week comparisons where you want to compare the same days in the previous year, you need to employ a different approach. This article will point you in the right direction.
Mynda
