Shouldn't SamePeriodLastYear give me EXACTLY the same number as calculated last year

[kmclorg]

I have created a SamePeriodLastYear calculation, then charted it to show my data by Quarter for the past three years.

Here is the calculation - which seems to work 

SamePeriodLastYear Policyholders = CALCULATE([Count of PolicyHolders],SAMEPERIODLASTYEAR('DIM Dates'[Date]))

and here is the formula for [Count of PolicyHolders]

Count of PolicyHolders = DISTINCTCOUNT(All_Policies[ClientNumber])

I have a date table in the data model and I thought this was working fine until I checked the numbers.

The problem is, that when I chart this information, it seems that the numbers don't always match.

For example:

2016 Q1 Count of PolicyHolders is 1436 but

2017 Q1 SamePeriodLastYear Policyholders is 1484

Shouldn't these be the same number?

Similar discrepencies exist with many of my other quarterly values. 

Is this simply because there are a different number of business days in Q2 last year vs this year and so the SamePeriodLastYear is spread over more or workdays? Or what could be causing the difference?

Thanks for any insight you can provide.

[v-ljerr-msft]

Hi @kmclorg,

Have you tried the solutions provided above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here.

Regards

[OwenAuger]

Just a few of basic checks:

• Does you 'DIM Dates' table contain complete calendar years and continguous sequence of dates?
• Is the 'DIM Dates'[Date] column (not some other column) related to the All_Policies table?
• Are all date filters you are applying on columns of the 'DIM Dates' table?
• If 'yes' to all of the above, then can you post a link to a sanitised workbook, or sample data that exhibit the problem?

Cheers,

Owen

[v-ljerr-msft]

Hi @kmclorg,

I have created a SamePeriodLastYear calculation, then charted it to show my data by Quarter for the past three years.

---

Could you try using PREVIOUSQUARTER function to see if it works in your scenario? 

SamePeriodLastYear Policyholders = CALCULATE ( [Count of PolicyHolders], PREVIOUSQUARTER ( 'DIM Dates'[Date] ) )

Regards