Yearly comparison with a not complete year

[Anonymous]

Hi everybody, 

I tried to find something on the community because I'm pretty sure that others have faced this issue.

I would like to make a comparison over year from 2017 until now. Obviously, 2020 is not yet completed so my chart do not represent what I want it to.

I would like to have a chart that represents the same amount of month that are filtered on the last year. 

Example : 

2017/01/01 -> 2020/03/31 

An average on 3 months for 2017,2018,2019 and the real amount for the 3 months of 2020.

Thanks in advance for your help

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

If you want to compare the average of 2017, 2018, 2019, 2020 with the first three months, We can try to Create a measure to meet your requirement:

Measure = CALCULATE(AVERAGE('Table'[value]),FILTER('Table',MONTH('Table'[Date]<MONTH(TODAY())-1))

If it doesn’t meet your requirement, could you please show some sample data and the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Assume your current measure is [real amount], you can try

measure =
var _max = month(maxx(Table,Table[Date]))
return
calculate([real amount], month(Table[Date])<=_max)

You can also use time intelligence with date calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete 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))

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/