How to filter two table with different dates

[Lurenz]

Hello, I've created a report with the forecast of sales, it works very fine, you can choose the period in the future and it shows all the data that we needed.

My boss asked me to put the amount of the sales of the last year of the same period of the forecast that you filtered.

I've imported in the data model the Sales table but i don't know how to do get the amount of the sales using the same filter of date of the forecast.

This is my model:

i tried the measure:

Sales Same period last year = CALCULATE(SUM(Sales_from_2019[vendita_totale]),SAMEPERIODLASTYEAR('Forecast cstm'[forecast_chiusura_prevista_c]))

and:

Sales Same period last year = CALCULATE(SUM(Sales_from_2019[vendita_totale]),SAMEPERIODLASTYEAR(DateCalendar[Date]))

but they don't work fine, the forecast's filtered data is not filtering the amount of sells of the last year

any ideas? Thanks

[amitchandak]

@Lurenz , 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.

[Lurenz]

@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