Margin % Calculation

Question

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 · Answer

alihijazi&nbsp;&nbsp;PY Sales = CALCULATE ( [Sales], PREVIOUSYEAR(CalendarTable[Date])&nbsp;YoY $ = [Sales] - [PY Sales]&nbsp;YoY % = DIVIDE ( [YoY $], [PY Sales], 0)

alihijazi · Answer

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 · Answer

Do you have a Calendar Table?&nbsp;If not visit MattAllington's link - http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/&nbsp;Time Intelligence Functions require the presence of a Calendar Table

alihijazi · Answer

I created a calendar using first =List.Date and then converting the list to table and &nbsp;I added the parts of the dates (year, month, week, quarter ...)&nbsp;but still it returns blankPlease advise

sean · Answer

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

Forum Discussion

Margin % Calculation

8 Replies

Related content

Calculating product profit margins

Margin % Calculation Column

% Margin calculation

Calculating invoice margin total

Calculating Gross Profit Margin