prior year MTD and YTD (same days last year)

[sschmidt]

I am trying to calculate prior month to date sales and year to date sales for the same days (not dates) last year.    What is the best way to do this?  I have a calendar table and included a calculated column in the calendar table that has the prior year days.  I don't know if adding the calculated column in the calendar table is the best way or even helpful in trying to accomplish what I am trying to do. 

For example 3/3/20 is 3/5/19.  So MTD this year includes (3/1/20, 3/2/20, and 3/3/20) and the MTD prior year should include (3/3/19, 3/4/19, 3/5/19).  

I just don't know what the best way to do this.  The sales information is in a separate table.  

Any help is greatly appreciated.

Thanks.  

[amitchandak · Accepted solution]

You can use time intelligence and date calendar for that. datesMTD and totalMTD can help. Same weekday 364 days behind

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year same weekday Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-364,DAY)))

Or

last year same weekday Sales = CALCULATE(SUM(Sales[Sales Amount]),(dateadd('Date'[Date],-364,DAY)))

last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,Year)))