Forum Discussion
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.
Click HERE for my .pbix file if needed.
Thanks a lot!
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.
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
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.
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
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:
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!