SamePeriodLastYear Not Working with Date Period Slicer

[AV155]

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:

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.

[tmantones]

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

[AV155]

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

[tmantones]

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.

[ADC-JasmineJ]

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]

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