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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

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

View solution in original post

4 REPLIES 4
Highlighted
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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

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

View solution in original post

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!


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 233 members 2,381 guests
Please welcome our newest community members: