Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |