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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vanessafvg
Super User
Super User

SAMEPERIODLASTYEAR not working as expected

I have this  calcualted measure, but it doesn't bring anything back - i can see data for last year so its a bit odd

PY Revenue = CALCULATE(sum(Sales[RevenueNettAmount]), SAMEPERIODLASTYEAR('Fiscal Date'[Date]))

 

Im obviously missing a link somewhere, any tips?

 

I have a filter Fiscal Month and Year, and then i am placing my date field on the graph in the right hand side where i was expecting to see the trend of last year too.   

Capture.PNG

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

My guess on this is that you are filtering Fiscal Date[Date] through your slicers and so when you feed it to SAMEPERIODLASTYEAR in a measure, it is filtered to a single month/year and thus all of last year's information is not there. I would try wrapping an ALL around 'Fiscal Date'[Date]) to remove any slicer filter context.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
nandic
Memorable Member
Memorable Member

Try this approach: mark you date dimension as date dimension (screenshot below).

 

Mark as date table 2.PNG

 

After having this, you use date column from this dimension to get same period last year.

Example: 

Total Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Table 'Date' is marked as Date dimension.
 
Result is list below:
Same Period Last Year list.PNG
Cheers,
Nemanja
 
Greg_Deckler
Super User
Super User

My guess on this is that you are filtering Fiscal Date[Date] through your slicers and so when you feed it to SAMEPERIODLASTYEAR in a measure, it is filtered to a single month/year and thus all of last year's information is not there. I would try wrapping an ALL around 'Fiscal Date'[Date]) to remove any slicer filter context.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Great solution! Thanks.

that definitely seems to be what the issue is, now if i could just get it to plot correctly on my line graph!  

 

thanks





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




From what I have observed if you do the following it does work:

 

1. The DATE table should have unique rows which contain ALL the dates from your dataset. So if your FACTS table cotains daily transactions from 2010 to 2017 with a few missing days the DATE table should contain those missing days as well.

2. Connect your DATE table with the FACTS table with the DATE key(ex: 01/03/2017) and not a DATID key

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.