Comparing values in table month by month for previous years

[artlemaks]

Hi all! 

I am a new user to Power BI. I am currently trying to create a graph that will compare values month by month for previous years. So for example, January 2020, compared to January 2019, compared to January 2018, for each store. I have started off with trying to compare year by year and then break it down into months. 

Here is what I have tried so far:

1. Created a calendar table called Date Table

2. I have a wastage_amount column which I have summed up to get a total for each year. 

3. I then created a new measure: 

Previours Year = CALCULATE(SUM('godigitally_easycoun_st store_wastage'[wastage_amount]), SAMEPERIODLASTYEAR('Date Table'[Date]))

Here are the results that I am getting. Please note, I have hidden the store names for privacy reasons: 

Sample Results 

How do I get the previous year to actually get the value for previous year? 

[amitchandak · Accepted solution]

@artlemaks , You can use time intelligence with date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[artlemaks]

@amitchandak you're a star.

This does exactly what I need it to do! I also realised that I was creating relationship between wrong tables and using the date from store_wastage table, rather than from the calendar table that I created. It seems like I am able to get the values that I need, thank you so much! 

Could you just explain the difference between those 2 for me? 

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))