cancel
Showing results for
Did you mean:

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

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.

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
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Memorable Member

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

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:
Cheers,
Nemanja

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

Great solution! Thanks.

Super User

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!

Resolver I

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors