relative date filter and sameperiodlastyear

[uncgmba]

hello!
i am trying to make a simple column chart by invoice date with sales CY and PY sales.  Works fine until i use the Last 90 days filter.  As i understand it, that filter will work in addition to the sameperiodlastyear and effectively eliminate the PY data.  I can't seem to find the exact issue in here but probably just me.  Can anyone point me in the correct direction?  FYI wrapping sameperiodlastyear in ALL isn't ideal because i need other filters slicers to be referenced.

Thanks

[uncgmba]

thanks for the response...the time intellgence works just fine until i put in a slicer of trailing 90 days on the date itself.  then PY goes away...i am pretty confident that it is what another person said...the slicer on the date is trumping the sameperiodlastyear function.  Trying to figure out how else to do it.

[amitchandak]

@uncgmba , That is because of sameperiodlastyear  and last 90 days.

So I am suggesting to use Datesytd or previousyear

[uncgmba]

right so back to my question...has anyone figured out a way to handle L90 with CY and PY sales?  Is there some other trick to get that visual to populate but with the remaining slicer definitions intact?
Thanks!

[amitchandak]

@uncgmba , every time intelligence function has way it takes date and work on that. So if you need Current Year Vs Last Year, see if time intelligence with date table can hel p

example

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Also refer why TI Fails : https://www.youtube.com/watch?v=OBf0rjpp5Hw

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.

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))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))