Margin % Calculation

[alihijazi]

Hello got the below table in which I'm displaying the sales by Engineer per year

what I want now is to calculate per engineer per year the Margin(%) which is calculated as follows (pseudo code):

for 2010: (sales (2010) - sales(2009)) / sales(2009)

if the Sales measure is defined as follows:

Sales = CALCULATE(sum(Transactions[cSales]),Transactions[SDLNTY]="SA" || Transactions[SDLNTY]="RG" )

what would be the sales of previous year?

Please advise

[Sean]

@alihijazi 

PY Sales = CALCULATE ( [Sales], PREVIOUSYEAR(CalendarTable[Date])

YoY $ = [Sales] - [PY Sales]

YoY % = DIVIDE ( [YoY $], [PY Sales], 0)

[alihijazi]

Can I share with you the file I'm working on?

it didn't work for me the previous year sales; it is giving blanks

[Sean]

Do you have a Calendar Table?

If not visit @MattAllington's link - http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

Time Intelligence Functions require the presence of a Calendar Table

[alihijazi]

I created a calendar using first =List.Date and then converting the list to table and  I added the parts of the dates (year, month, week, quarter ...) 

but still it returns blank

Please advise

[Sean]

@alihijazi so you created a separate Calendar Table? Did you create a Relationship between the 2 tables?

[alihijazi]

indeed @Sean

[Sean]

@alihijazi  If you have the Calendar table set up as described in Matt's link I provided above - evertything should work

I tried to make my sample data as close as possible to your example and I'm including the formulas again

Everything should work okay - don't you get anything to work at all?