Solved: Difference between two measures

Eagle · ‎06-01-2017

Hi,

I am so happy that I found this forum. I have on question.

In power querry I have one column called Value which has few attributes like quantities, sales, COGS. In this querry we can find data for ACT period and last year. By creating explicit measures we created Calculated fields MTD/YTD/YTG/FY all referenced to this value column.

So basically our file is capable of reuturning number of Pieces sold, NSR and COGS for specific month selected in calendar table for PY and actuals. (for November it gives Nov MTD, YTD, FY, YTG)

Last this which bothers us is how to create one additional field to return difference between let's say NSR MTD Nov of this year vs PY. Let's start from the fact is it possible.

If needed I can give overview of file and formulas. Hope you can understand me. It is not easy for me to understand myself 🙂

Regards,

Filip

Anonymous · ‎06-01-2017

The important realization (I think...?) is that measures can references other measures.

Sales := SUM(Transactions[SaleAmt])

SalesPY := CALCULATE([Sales], SAMEPERIODLASTYEAR(Dates[Date])

Sales Delta := [Sales] - [SalesPY]

So, the general answer to "how do I take the difference between 2 measures" is... well, just subtract them 🙂

View solution in original post

Anonymous · ‎06-01-2017

The important realization (I think...?) is that measures can references other measures.

Sales := SUM(Transactions[SaleAmt])

SalesPY := CALCULATE([Sales], SAMEPERIODLASTYEAR(Dates[Date])

Sales Delta := [Sales] - [SalesPY]

So, the general answer to "how do I take the difference between 2 measures" is... well, just subtract them 🙂

Anonymous · ‎10-08-2019

@AnonymousCan you offer any pointers on how to solve for the below scenario? I have a clustered column chart where I'm pulling in Revenue, and slicers created by What If parameters for changes in price and demand. I want to calculate the impact on revenue after changes in price and demand, which I can do if the changes in price and demand are both positive. But if the change in either price or demand are negative, adding the difference between price and demand won't work (see second table). I'm not looking for anyone to solve this for me but I would love to hear opinions on which DAX functions to use to handle the measure I'm creating when there are negative values in either price or demand.