DAX for Subtracting Current Year Sales from Previous Year Sales

[jderekc]

Hi,

I have hit a roadblock with DAX.  I have a matrix in which I have three "year" columns, but one is actually a "DELTA" column, meaning how much something has changed.  For instance (ignore the actual numbers and especially the profit % as I am only provided example data that isn't necessarily correct mathematically):

Period     |     NetSales     |     Units     |     GrossProfit     |     Profit %

________________________________________________________________________

2017       |   $20,000,000  |  132,500    |   $8,000,000       |   19.42

2016       |   $19,400,000  |  130,000    |   $7,600,000       |   19,38

DELTA     |   $     600,000 |      2,500    |   $    400,000      |     0.04

________________________________________________________________________

The number in bold I can't get to calculate correctly or at all.  Basically in this matrix, I introduced a dummy "year" as the period in order to get the change associated with 2017 versus 2016.  In SQL, I can get the "DELTA" for NetSales, Units, and GrossProfit, but it is much more difficult to get Profit %.  Thus, I have DAX formulae that calculate this and do a good job for 2017 and 2016 but not for "DELTA".  It does the same calculation on "DELTA" but that doesn't yield a difference of 0.04%.  It is its own profit margin percentage.

Anyway, I am trying to get DAX up and running where I can subtract 2016 gross profit margin from 2017.  I have a DAX query as follows for two measures.  One for this year and one for the previous year.  It ends up populating results, but nets to zero as I presume it calculates all data instead of just calculating data that is associated with 2017 (for current year) and then data that is associated with 2016 (for previous year) and subtracting them.

Here are the DAX queries I am using.  I think I am on the right track... maybe... but have hit a brick wall.  Any help is appreciated!

CYProfit% = IF(DISTINCT(BySupplier[Period]) = "2017", 
100*(1-(SUMX(FILTER(BySupplier, RELATED(PeriodOrder[Period]) = "2017"), BySupplier[NetSales]) - 
SUMX(FILTER(BySupplier, RELATED(PeriodOrder[Period]) = "2017"), BySupplier[GP]))) / 
SUMX(FILTER(BySupplier, RELATED(PeriodOrder[Period]) = "2017"), BySupplier[NetSales]), 
100*(1-(SUMX(FILTER(BySupplier, RELATED(PeriodOrder[Period]) = "2017"), BySupplier[NetSales]))) - 
SUMX(FILTER(BySupplier, RELATED(PeriodOrder[Period]) = "2017"), BySupplier[GP]) / 
SUMX(FILTER(BySupplier, RELATED(PeriodOrder[Period]) = "2017"), BySupplier[NetSales]))

Current year profit margin

Previous year profit margin is exactly the same except "2017" is replaced with "2016"

Overal

Profit % = IF(100*(1-(sum(BySupplier[NetSales]) - 
sum(BySupplier[GP]))/sum(BySupplier[NetSales])) =0, "",
if(1-(sum(BySupplier[NetSales]) - sum(BySupplier[GP]))/
sum(BySupplier[NetSales]) =1 || ISERROR(1-(sum(BySupplier[NetSales]) - 
sum(BySupplier[GP]))/sum(BySupplier[NetSales])) = TRUE() ,"", 
IF(100*1-(sum(BySupplier[NetSales]) - sum(BySupplier[GP]))/
sum(BySupplier[NetSales]) = 0, BLANK(),
IF(DISTINCT(BySupplier[Period]) = "DELTA", [CYProfit%] - [LYProfit%],
100*(1-(sum(BySupplier[NetSales]) - sum(BySupplier[GP]))/sum(BySupplier[NetSales]))))))

Overall profit margin measure

Thanks!

Best regards,

Derek

[BILASolution]

Hi @jderekc

I think, this is what you're looking for...

The next DAX calculates the profit %, you need to modify it with your own fields and tables.

Profit % = 

var profit2016 = CALCULATE(DIVIDE([Total Gross Profit];[Total Net Sales]);Sales[Period] = "2016")

var profit2017 = CALCULATE(DIVIDE([Total Gross Profit];[Total Net Sales]);Sales[Period] = "2017")

var period = FIRSTNONBLANK(Sales[Period];1)

return

IF(period = "DELTA";profit2017 - profit2016;DIVIDE([Total Gross Profit];[Total Net Sales]))

I hope this helps

Regards

BILASolution

[jderekc]

Thanks @BILASolution!  That helped me out a bit, but I cannot get it to work.  I did something similar to what you proposed but did not get good results.  I had to create measures from the raw data columns and to do this I simply summed the raw data columns in my measure creation.  Thus, "GPM" is a measure with a formula of:

GPM = SUM(BySupplier[GP])

 Main DAX formula:

Profit %2 = 
 var profitLY = CALCULATE(DIVIDE([GPM], [NetSalesM], 0), BySupplier[Period] = "2016")
 var profitTY = CALCULATE(DIVIDE([GPM], [NetSalesM], 0), BySupplier[Period] = "2017")
 var period = FIRSTNONBLANK(BySupplier[Period], 1)
return
IF(period = "DELTA", profitTY - profitLY, (DIVIDE([GPM], [NetSalesM])))

The profit margins seem excessive (some are over 100%).  Also, I don't think the formula is recognizing "DELTA" as a period, because it seems to be skipping the "profitTY - profitLY" calculation and going instead directly to the end calculation.

Thanks!

- Derek

[BILASolution · Accepted solution]

Hi @jderekc

Try this...

Profit %2 = 
 
var profitLY = 
	CALCULATE
	(
		DIVIDE
		(
			SUMX(BySupplier, BySupplier[GP]), 
			SUMX(BySupplier, BySupplier[NetSales])
		), BySupplier[Period] = "2016")
	)
var profitTY = 
	CALCULATE
	(
		DIVIDE
		(
			SUMX(BySupplier, BySupplier[GP]), 
			SUMX(BySupplier, BySupplier[NetSales])
		), BySupplier[Period] = "2017"
	)

var period = FIRSTNONBLANK(PeriodOrder[Period], 1)

return

IF
(
	period = "DELTA", 
	profitTY - profitLY,
	DIVIDE
	(
		SUMX(BySupplier, BySupplier[GP]), 
		SUMX(BySupplier, BySupplier[NetSales])
	)
)

[jderekc]

Interestingly I get the following (profit %2 is what I used with your recommendations; you can ignore profit %).

[BILASolution]

@jderekc

I need to know what kind of visual you are using, because my DAX expression works in tables with the Period field included.

[jderekc]

Hi @BILASolution,

I apologize.  I am using the original matrix visualization.  For instance:

Rank    |             Supplier           |     Period    |     NetSales    |    Units    |      GP      |    Profit %

__________________________________________________________________________________________________

1          |   ToysNCrafts              | 

                                                 |    2017        |  $25,000         |     1675   |  $4,700    |   15.00

                                                 |    2016        |  $23,000         |     1650   |  $4,300    |   14.00

                                                 |   DELTA      |  $2,000            |         25   | $400        |    1.00

2          |   ArtsyStuff                  | 

                                                 |    2017        |  $5,000           |    700      |  $4,000     |   18.00

                                                 |    2016        |  $4,000           |    600      |  $3,300     |   17.50

                                                 |   DELTA      |  $1,000            |    100      |  $700        |    0.50

3          |   MegaGames             | 

                                                 |    2017        |  $55,000         |     4000   |  $12,500   |   19.00

                                                 |    2016        |  $33,000         |     3500   |  $12,000   |   16.00

                                                 |   DELTA      |  $22,000           |      500   |  $500       |    3.00

I know Microsoft has since changed their matrix visuals to where you have to drill down I think.  But this display is how we prefer to see the data.

[BILASolution]

@jderekc

I test the DAX Expression with a matrix visual and it works.

[jderekc]

Thanks!  I must be doing something wrong.  I will continue to try to use the DAX formulae you have provided and will get back once I've exhausted those attempts.  I sincerely appreciate your help.

[v-huizhn-msft]

Hi @jderekc,

Have you resolved your issue according to solution @BILASolution posted? Please mark it as answer if you have.

Thanks,
Angelia

[jderekc]

My issue persists, but I think the solution is sound.  It might just be something in my environment.  I'll accept the solution.

[BILASolution]

@jderekc

You're welcome