compare sales offices

Question

hi, I write you to ask you a help.I must compare the sales of the offices for year, but I have a problem when an office doesn't result to have sales in one year.I have a field id, year and office example 2017office1 2018office1....id&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; idprec2017office1&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;2016office12018office1&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;2017office1through the formula calculate (sum (sales); filter (TAB1;ID=EARLIER (IDPREC)) &nbsp;if an office doesn't have sales for the year 2018 it doesn't suit me to look for the possible value of the year before andtherefore it doesn't notice the differencehelp me!

anonymous · Answer

romeo95,Please check if the following DAX return your expected result, if not, please describe more about your desired result.
previousyearsales = CALCULATE(FIRSTNONBLANK(TAB1[sales],1),FILTER(TAB1,TAB1[id]=EARLIER(TAB1[id])&amp;&amp;TAB1[year]=EARLIER(TAB1[year])-1))
diff = IF(ISBLANK(TAB1[sales])||ISBLANK(TAB1[previousyearsales]),BLANK(),TAB1[sales]-TAB1[previousyearsales])
Regards,Lydia

romeo95 · Answer

thank you, I'll try it soon as possible

romeo95 · Answer

Hi,it doesn't workThe problem is thisid&nbsp;&nbsp; &nbsp;sales&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;201701/office1&nbsp;&nbsp; &nbsp;100&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;201702/office1&nbsp;&nbsp; &nbsp;55&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;201801/office1&nbsp;&nbsp; &nbsp;75&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2017&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; year&nbsp;&nbsp; &nbsp;year-1&nbsp;&nbsp; &nbsp;year&nbsp;&nbsp; &nbsp;year-1&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; month&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;office1&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;1&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 75&nbsp;&nbsp; &nbsp;100&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 55&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;total&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 155&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 75&nbsp;&nbsp; &nbsp;100There aren't sales for 201802 and the total year-1 of 2018 doesn't calculate itNow I try to insert the no match record with 0 as sales

anonymous · Answer

romeo95,You add Month field this time. Please share your PBIX file with dummy data to me so that I can provide you exact DAX.You can upload PBIX file to OneDrive and post shared link of the PBIX file.Regards,Lydia

Forum Discussion

compare sales offices

4 Replies

Related content

Comparing Two Sales Tables

compare sales and percentage changes in sales

Compare previous and new sales

New and lost Sales comparing periods

Comparable sales