Hi all,
I previously asked a question about DAX in a thread titled "DAX for Subtracting Current Year Sales from Previous Year Sales" seen here: DAX-for-Subtracting-Current-Year-Sales-from-Previous-Year-Sales. Well I haven't been able to fully get it to work. The problem seems to be that DAX isn't understanding my request for if a period (2017, 2016, or DELTA) satisfies a boolean condition. It simply seems to say, "Nope... I don't see a period named "DELTA" so I will proceed with the other calculation). This is my DAX query:
ProfitTwo =
var profitLY = CALCULATE(DIVIDE(SUMX(BySupplier, BySupplier[GP]), SUMX(BySupplier,
BySupplier[NetSalesM]), 0), BySupplier[Period] = "2016")
var profitTY = CALCULATE(DIVIDE(SUMX(BySupplier, BySupplier[GP]), SUMX(BySupplier,
BySupplier[NetSalesM]), 0), BySupplier[Period] = "2017")
var period = FIRSTNONBLANK(PeriodOrder[Period], 1)
return
IF(period = "DELTA", profitTY - profitLY, (DIVIDE(SUMX(BySupplier, BySupplier[GP]),
SUMX(BySupplier, BySupplier[NetSales]))))
So here is the problem. I had 2017 sales with a profit margin of let's say 25.00%. I had 2016 sales with a profit margin of 25.25%. I want the "DELTA" row in the matrix to show the difference which is -0.25%. Instead, it is simply calculating the DELTA row's gross profit difference divided by the net sales difference.
NOTE: The DELTA row has SQL code that populates the net sales, units sold, and gross profit columns. The only other thing to do is populate the profit margin % which I can't seem to do in DAX. Also, since this is a calculation based on two separate periods, DAX was the best way for me to do profit margin %.
One of the things I am confused about is the "FIRSTNONBLANK" in DAX and especially this line:
var period = FIRSTNONBLANK(PeriodOrder[Period], 1)
I am not 100% sure what it is doing. If anyone can explain, I'd love that!
What do you think? Any ideas?
Best regards,
Derek
