Problem with a Year on Year Comparison

[shekhar_shres]

Dear Community,

I have sales data dating back from 2010. I used line and stacked column chart with total sales(as a bar) by year and Growth % (as a line). Everything is working out great until we are here today at 9/06/2021. 

The total of sales from 1/1/2021 to 9/6/2021 is $ 200,000, which is great, Power BI is showing that correctly.

But the total of sales from 1/1/2020 to 9/6/2020 is $ 145,000, but Power BI is taking $ 160,000 (1/1/2020 to 30/6/2020). It is taking full month for June 2020 which doesn't make sense with the Dax I am using. This is understating the growth % that I am showing.

Following are the Dax I am using 

TotalSales = Sum(SalesData[Amount])

Sales Last Year = CALCULATE('Total Measures'[TotalSales], DATEADD('Date Table'[Date], -1, YEAR))

Difference =

Placements Y.Difference =

VAR Curry = [TotalSales]

VAR Lasty = [Placements Last Year]

Var Difference = Curry - Lasty

Return

Difference

Note: When I create a table and Go line by line, the line by line value sums to $145,000 but the grand total somehow is $160,000 which is funny.

What can I do to compare it to the right period that is comparing (2021/1/1 - 2021/6/9) to (2020/1/1 - 2020/6/9) 

[v-xiaotang]

Hi @shekhar_shres 

Have you solved your problem? If yes, kindly accept it as the solution. Otherwise, please provide more details about your problem so that we can work on it further. Thanks😃

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[v-xiaotang]

Hi @shekhar_shres 

"It is taking full month for June 2020" —— because function DATEADD() will return the last whole month, FYI:
https://docs.microsoft.com/en-us/dax/dateadd-function-dax

One quick example,

if you want period (1/1/2020 to 30/6/2020), use function filter() mentioned by amit.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[amitchandak · Accepted solution]

@shekhar_shres , if date range is not selected on page, try a measure like

Sales Last Year =
var _max1 = maxx(allselected(sales), sales[date])
var _max = date(year(_max1)-1, month(_max), day(_max))
return
CALCULATE('Total Measures'[TotalSales], DATEADD('Date Table'[Date], -1, YEAR),filter('Date Table','Date Table'[Date]<=_max ))