cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccakjcrx Member
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. 

 

Screenshot.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

Click HERE for my .pbix file if needed. 

 

Thanks a lot!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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:

 

https://www.linkedin.com/pulse/mean-time-between-failure-mtbf-power-bi-greg-deckler-microsoft-mvp-

 

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.


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

Proud to be a Datanaut!


Interkoubess Established Member
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
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:

 

https://www.linkedin.com/pulse/mean-time-between-failure-mtbf-power-bi-greg-deckler-microsoft-mvp-

 

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.


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

Proud to be a Datanaut!


Interkoubess Established Member
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
Member

Re: Measure: Subtracting prior value in same column

Hey @Interkoubess@Greg_Deckler!

 

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:

 

Solution.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
Super User

Re: Measure: Subtracting prior value in same column

Nice! Time intelligence functions to the rescue!


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

Proud to be a Datanaut!