Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Eagle
New Member

Difference between two measures

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

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

2 REPLIES 2
Anonymous
Not applicable

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 🙂

@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.

 

Revenue5
Change in Price5
Change in Demand11
Sum of changes in price and demand relative to revenue6
Revenue plus Changes in Price and Demand11

 

Revenue5
Change in Price-5
Change in Demand11
Sum of changes in price and demand relative to revenue16
Revenue plus Changes in Price and Demand21

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.