Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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.
Proud to be a Super User!
@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.
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
You can test this very quick!
Import your data model with the working SAMEPERIODLASTYEAR in PBI and check if you get the same result!
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.
Proud to be a Super User!