Forum Discussion
Same period last year not working while filter both ways
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.
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.