Same period last year not working while filter both ways

[Anonymous]

Hello,

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

Sales Date

Delivery Date

Sales $

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!

BR,

Otter

[amitchandak]

@Anonymous , 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 :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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.

[Anonymous]

Hello,

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.