cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Compare Last year months data VS this year Months

Hello, 
 
I have created two formulas to compare last year month vs this year months sales data. It works fine, but when it comes to future month(2018) it will show me the same value as last year instead of blank. Can anybody please tell me how to fix this or any better way to get do this? 
 
Sales This Year = TOTALYTD(SUM(SalesDetails[Sales]),SalesDetails[Invoices.Day])
Sales Last Year = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Calendar_dim[Date]))

After March 2018 should be  blank, but it is not.After March 2018 should be blank, but it is not.

 Thank you so much

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

In my opinion, the issue is related to your chart axis. The Axis in your chart is MonthNameLong which only shows the month. It also means, your chart cannot tell which year the month belongs to. As in your source table, there exists data in April 2017. So there will exist data in April.

 

To resolve your issue, please try to add Year and Month as Axis in chart.

 

Thanks,
Xi Jin.

View solution in original post

6 REPLIES 6
Super User III
Super User III

Hi,

 

Try this

 

Sales This Year = IF(ISBLANK(SUM(SalesDetails[Sales])),BLANK(),TOTALYTD(SUM(SalesDetails[Sales]),Calendar_dim[Date]))

 

Ensure that the months appearing in your visual are from the Calendar_dim table.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

 

Hello Ashish, 

 

Thank you so much for the help. when I use the date from calendar instead of date from sales it will not accurate somehow. Please look at the file below. I tried what you have suggested, still the same. 

 

 

 

Thank you so much 

Hi,

 

Reduce that file size to a couple of MB's please.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

 

Hello, 

 

I really appreciate your help.  I have deleted some records and you can see it from HERE. I think I reduce the file size to less than 3.5 MB. 

 

Thank you so much 

 

Hi @Anonymous,

 

In my opinion, the issue is related to your chart axis. The Axis in your chart is MonthNameLong which only shows the month. It also means, your chart cannot tell which year the month belongs to. As in your source table, there exists data in April 2017. So there will exist data in April.

 

To resolve your issue, please try to add Year and Month as Axis in chart.

 

Thanks,
Xi Jin.

View solution in original post

Anonymous
Not applicable

@v-xjiin-msft

 

Hey Xi Jin,

 

Thank you so much for the advice. When I Just add sales to measures and drag year into legend it will fix my issue. 

 

Again, thank you 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors