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. 

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


Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 276 members 2,735 guests
Please welcome our newest community members: