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.

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.

Proud to be a Datanaut!

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.

Proud to be a Datanaut!

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

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

Proud to be a Datanaut!

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 169 members 2,429 guests
Recent signins: