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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AV155
Frequent Visitor

SamePeriodLastYear Not Working with Date Period Slicer

Hello,

 

I have a Date table called Dates.   I have a Date Periods table which contains periods like MTD, Last Week, etc, which is setup like this:

temp Power BI1.png

 

 

 

 

 

 

 

 

 

 

etc.

 

I have a slicer based on this Date Periods table.

 

In my main data table, I have a measure to calculate the Order Count for the same period last year.  This works fine if nothing is selected in my slicer.

 

OrderCount LY = CALCULATE(COUNT(DataTable[OrderNo]), ALL(Dates[Date]), SAMEPERIODLASTYEAR(Dates[Date]))
 
 
I see the problem is that when I select a value in the Slicer, it is filtering out the dates not in that range so it doesn't see the orders in the previous year.  I don't know how to solve this though.
5 REPLIES 5
tmantones
Regular Visitor

I'm having the same issue and the suggested solution does not fix the problem. @AV155 Were you ever able to figure this out?

Not really.  I ended up changing some database views to accomodate for it, never got it fully working correctly in Power BI alone.... 

I had a revelation last night while working on my problem and was able to get it to work. Maybe it will work for you.
I found numerous forum posts similar to yours where people were being told to use the ALL function to clear their filters since the dates being filtered prevented the SAMEPERIODLASTYEAR function from being able to see any data for the prior period. The ALL function never worked for me when using it on the 'Calendar' table. However, I forgot that my relative time slicer is actually powered by a separate table entirely. Once I applied the ALL function to that table as well, it worked.

Prior Period Metric = CALCULATE(
SUM('Metric Table'[Metric]),
SAMEPERIODLASTYEAR('Calendar'[Date]),
ALL('Calendar'), ALL('Relative Time Select Slicer')
)

Now, I don't know if you have your relative time period stuff setup in a separate table like I did, but I thought I would share this with you in the hopes that it helps in some way. Good luck.

WOW -- THIS WORKED!!! I've been scratching my head for 3 days and abusing the google search with various search iterations trying to get it. Thank you so much!!

amitchandak
Super User
Super User

@AV155 , if you are selected date/period from the same date table used in measure, it should work

 

OrderCount LY = CALCULATE(COUNT(DataTable[OrderNo]), SAMEPERIODLASTYEAR(Dates[Date]))

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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