Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Same period last year not working while filter both ways



Currently I have a fact table that consists of Sales Date, Delivery Date and Sales $

Sales DateDelivery DateSales $


I have 2 filter tables that has calendar to filter Sales Date and Delivery Date:

Sales Date
Delivery Date


I used relationship filtering both way between 3 tables, because I want Sales Date to also filter Delivery Date


I want to then have Same Period Last Year based on Sales Date:

Sales $ LY = CALCULATE(SUM('Fact table'[Sales $]), SAMEPERIODLASTYEAR('Sales calendar'[Sales Date]))

 However it has thrown error: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection.


I tried to bypass by putting:

Sales $ LY = IF(SELECTEDVALUE('Sales calendar'[Sales Date]) = BLANK(), BLANK(),
CALCULATE(SUM('Fact table'[Sales $]), SAMEPERIODLASTYEAR('Sales calendar'[Sales Date])))

It didn't work.


Is there any idea how I can overcome this?


Thank you beforehands!




Super User
Super User

@otter , You need to have a date table join both dates with the date of the date table(I think sales calendar). One join will be inactive. Make sure it marked as date table.


Assume delivery join is inactive


Then LY can be

Sales $ LY = CALCULATE(SUM('Fact table'[Sales $]), SAMEPERIODLASTYEAR('Date'[Date]))



Sales $ LY delivery = CALCULATE(CALCULATE(SUM('Fact table'[Sales $]),userelationship('Date'[Date], 'Fact table'[delivery Date] )), SAMEPERIODLASTYEAR('Date'[Date]))



Other options are

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))



Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :
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.



My relationships are like this:

Fact table <> Sales calendar (two way relationship)

Fact table <> Delivery calendar (two way relationship)


None of the relationships are inactive.


I want that I can use Date filters for either Sales Date and Delivery Date as separate Filters. I.e. when I choose a Sales Date, it will show different Delivery Dates in the other filter and vice versa.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors