cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shekhar_shres
Frequent Visitor

Problem with a Year on Year Comparison

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) 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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 ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

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
Community Support
Community Support

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,

v-xiaotang_0-1623750820104.png

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
Super User IV
Super User IV

@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 ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors