Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
przemekkucia
Advocate II
Advocate II

Same period last year - works differently than in power pivot

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!

1 ACCEPTED SOLUTION
kcantor
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
przemekkucia
Advocate II
Advocate II

@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
Community Champion
Community Champion

@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

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Sean
Community Champion
Community Champion

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
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.