Same period last year - works differently than in power pivot

[przemekkucia]

Hello,

I have used SamePeriodLastYear successfully in power pivot table in Excel and it worked like magic.

I tried to implement the same formula to the same source through Power BI desktop and it works up to the point when I'm starting to slice the result by anything (category for example; which again, worked in Pivot Table).

I tried (probably) every workaround available on the Internet (from dateadd, through hasonevalue to custom filtering) and now goes my set of questions: Is this a normal behaviour for Power BI Desktop? Is PBI as limited in comparison with Excel? Maybe someone knows if this is a know issue and Microsoft is working on it?

Edit and Solution: Pover Pivot and Power BI handle relationships slightly differentely. I had to change the relationship between FactTable and DateTable to one way (Many to One) in order for it to behave exactly as my Power Pivot mirror file. Thanks @kcantor for pointing my awareness in the right direction!

[przemekkucia]

@Sean - It's a direct import from a database view. I could try and make a sample table, and next time I'll get to some hurdles I shall use this "sampling method" of yours 🙂 Thank you sir!

@CheenuSing - I tried from scrach few times before I posted a question 😉 Thanks nonetheless, you are as kind as you are wise.

@kcantor - Thank you! I overlooked/have been unaware of the different method in which Power Pivot and Power BI handle relationships.

[CheenuSing]

@przemekkucia

1. If you have a date table mark it as a DateTable in the modelling tab.

2. The date table should have a column called DateKey of the format YYYYMMDD.

3. Fact tables should have column for datekey of the same formformat YYYYMMDD.

4. The Fact and Date table should be joined ( set relationship) using the datekeys.

5. Now when you try SAMEPERIOD lastyear it should work.

6,. I guess you are using the expression like Calculate ( [Sales], SAMEPERIODLASTYEAR(DateTable[FullDate])

Try it out.

If it works please accept it as a solution and also give kudos.

Cheers

CheenuSing

[Sean]

You can test this very quick!

Import your data model with the working SAMEPERIODLASTYEAR in PBI and check if you get the same result!

[kcantor · Accepted solution]

Have you tried checking the direction of your relationship with your date table? PowerBI allows a little more flexibility in bi-directional relationships. Set the relationship to mirror what you have in PowerPivot and give it another try.