Forum Discussion
How to filter two table with different dates
Super UserLurenz , you have date table you can use time intelligence
example - change based on need
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
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.
amitchandak Thank you for those measures, but i think i need something more specific because i need to compare not only year vs year but weeks, days or months.
Here an example:
I want to show all the forecast of sells from 29th of October 2020 to 29th of November 2020, and this is ok!
meanwhile i have to put the amount of the sells from 29th of October 2019 to 29th of November 2019, I don't find the right measure to calculate this amount using date filters that i used to show the forecast!
Thank you