Forum Discussion
Subtracting values from the same column but different rows
Hi!
I have an electricity meter that is measuring cumulatively but I need to calculate the difference, therefore, the electricity use each hour. All the electricity data is in one column and I need to, for example, calculate the difference between Row 2-Row 1 for all of the rows. Is there a way to do this in Power BI?
Many thanks for any help you can give me!
Hi Anonymous,
Take below simple data as an example.
To get the difference between rows via calculated column.
diff = Table3[Value] - CALCULATE ( SUM ( Table3[Value] ), FILTER ( Table3, Table3[Index] = EARLIER ( Table3[Index] ) - 1 ) )To get difference between rows via measure.
diff measure = SELECTEDVALUE ( Table3[Value] ) - CALCULATE ( SUM ( Table3[Value] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Index] = SELECTEDVALUE ( Table3[Index] ) - 1 ) )For more advice, please provide your sample data and desired output. How to Get Your Question Answered Quickly
Best regards,
Yuliana Gu
4 Replies
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Hi Anonymous,
Take below simple data as an example.
To get the difference between rows via calculated column.
diff = Table3[Value] - CALCULATE ( SUM ( Table3[Value] ), FILTER ( Table3, Table3[Index] = EARLIER ( Table3[Index] ) - 1 ) )To get difference between rows via measure.
diff measure = SELECTEDVALUE ( Table3[Value] ) - CALCULATE ( SUM ( Table3[Value] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Index] = SELECTEDVALUE ( Table3[Index] ) - 1 ) )For more advice, please provide your sample data and desired output. How to Get Your Question Answered Quickly
Best regards,
Yuliana Gu
Hi Yuliana,
If i want to make Row 1 - Row 2 to get the difference, how should the measure look like?
Thank you in advance
Hi, I tried this and getting error message...
MdxScript(Model) (12,162) Calculation error in measure
