Forum Discussion
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.
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!