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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.