SAMEPERIODLASTYEAR doesn't work with discontinued timeline

[haianhcao]

I have a Date Table that has a relationship with Sales Forecast table. 

I'm using 2 slicers with month and year columns from the Date Table.

My code for Revenue:

Revenue = 
VAR Revenue = CALCULATE(SUM('01_Sales Forecast'[Amount]), '02_Opportunity'[Probability]=100)
RETURN IF(ISBLANK(Revenue), 0, Revenue)

My code for Revenue Last Year:

Revenue LY = 
VAR RevenueLY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('00_Date Table'[Date]))
RETURN IF(ISBLANK(RevenueLY), 0, RevenueLY) 

When I filter the slicer with a continued timeline, everything works fine.

 

However, when I deselect any month in the middle that cause the timeline to be discontinued, my visuals show errors.

How should I fix this?

[Ashish_Mathur]

Hi,

What is the error you see when you click on the See Details hyperlinked text?

[amitchandak · Accepted solution]

@haianhcao , Make sure the join between date table and sales forecast should be single direction. If it bi-directional it can give error like this.

If that is not the case share error from view error 

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...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[haianhcao]

It works! Thank you. 🙏