Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ccakjcrx
Resolver I
Resolver I

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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
ccakjcrx
Resolver I
Resolver I

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. 

Nice! Time intelligence functions to the rescue!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.