cancel
Showing results for
Did you mean:
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!

Highlighted
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!

Highlighted
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

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.

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

Community News & Announcements

Get your latest community news and announcements.

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 303 members 3,210 guests
Recent signins: