cancel
Showing results for
Did you mean:
ccakjcrx Member

Measure: Subtracting prior value in same column

Hello!

Although it has proven difficult for me, I have what seems to be a simple problem. I want to create a measure to subtract the prior value in the same column from the current row. In the screenshot below, I show two columns for columns I have in my table. I also show a column (labeled Difference) that represents the measure I want to create. I show the logic I want to use to create the column. Click HERE for my .pbix file if needed.

Thanks a lot!

2 ACCEPTED SOLUTIONS

Accepted Solutions Super User

Re: Measure: Subtracting prior value in same column

Generally you accomplish something like that in a Column with doing an EARLIER. I have an example of that here:

Now, if you want to do that in a measure, you will probably need to use one of the "X" functions like SUMX, MAXX, etc. in conjunction with an EARLIER.

I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Interkoubess Established Member

Re: Measure: Subtracting prior value in same column

Hi @ccakjcrx,

As proposed by @Greg_Deckler you can create a calculated column ( I called it Difference) as below:

Difference = Sheet1[value]-CALCULATE(MAX(Sheet1[value]),FILTER(Sheet1,Sheet1[date]<EARLIER(Sheet1[date])))

You can improve it for the first line when it is blank like this:

Difference = if(ISBLANK(CALCULATE(MAX(Sheet1[value]),FILTER(Sheet1,Sheet1[date]<EARLIER(Sheet1[date])))),BLANK(),Sheet1[value]-CALCULATE(MAX(Sheet1[value]),FILTER(Sheet1,Sheet1[date]<EARLIER(Sheet1[date]))))

Let us know it does not work...

Ninter

4 REPLIES 4 Super User

Re: Measure: Subtracting prior value in same column

Generally you accomplish something like that in a Column with doing an EARLIER. I have an example of that here:

Now, if you want to do that in a measure, you will probably need to use one of the "X" functions like SUMX, MAXX, etc. in conjunction with an EARLIER.

I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Interkoubess Established Member

Re: Measure: Subtracting prior value in same column

Hi @ccakjcrx,

As proposed by @Greg_Deckler you can create a calculated column ( I called it Difference) as below:

Difference = Sheet1[value]-CALCULATE(MAX(Sheet1[value]),FILTER(Sheet1,Sheet1[date]<EARLIER(Sheet1[date])))

You can improve it for the first line when it is blank like this:

Difference = if(ISBLANK(CALCULATE(MAX(Sheet1[value]),FILTER(Sheet1,Sheet1[date]<EARLIER(Sheet1[date])))),BLANK(),Sheet1[value]-CALCULATE(MAX(Sheet1[value]),FILTER(Sheet1,Sheet1[date]<EARLIER(Sheet1[date]))))

Let us know it does not work...

Ninter

ccakjcrx Member

Re: Measure: Subtracting prior value in same column

I accepted both of your replies as solutions because they both were helpful. I found an alternate way. I REALLY felt like I would need to use EARLIER in some fashion; not only based on input received from both of you, but also because that was here my head was at prior to posting. Here is my final table: Here was the measure I was able to come up with:

DailyDifference =
SELECTEDVALUE(Sheet1[value]) -
CALCULATE(
SUM(Sheet1[value]),
PREVIOUSDAY(Sheet1[date])
)

Thanks for working the boards and helping all of us out. I hope you both have a great weekend.

Highlighted Super User

Re: Measure: Subtracting prior value in same column

Nice! Time intelligence functions to the rescue!

I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 221 members 2,353 guests
Recent signins:
• samartinez • facilitydax • rekstrom601 • AugustoRadames • Jkilb • Nishantjain • mstranseth • nielsentm • jomagarcia • JParker66 • Sadiq9349 • Ali-Aati • remcodegroot • shilparajesh 