Forum Discussion
Grouping / Aggregation
I have the data below and was wondering how to calculate the diff column which is previous qty minus current row quantitiy while ensuring ID is the same. Many thanks!
| ID | RecordDate | Accumilated Qty | Diff |
| 5 | 06/01/2019 | 4557 | |
| 5 | 13/01/2019 | 4586 | 29 |
| 5 | 20/01/2019 | 4609 | 23 |
| 5 | 27/01/2019 | 4621 | 12 |
| 5 | 10/02/2019 | 4630 | 9 |
| 5 | 17/02/2019 | 4646 | 16 |
| 5 | 24/02/2019 | 4658 | 12 |
| 5 | 03/03/2019 | 4658 | 0 |
| 5 | 10/03/2019 | 4658 | 0 |
| 5 | 17/03/2019 | 4690 | 32 |
| 5 | 24/03/2019 | 4714 | 24 |
| 5 | 30/03/2019 | 4741 | 27 |
5 Replies
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Thanks, looked at EARLIER but im finding it difficult to apply... hence the post. 😁
Hi,
I suggest you to create an Index column, this can make it very simple with the following formula:
Result = VAR current_ = [Index] RETURN IF ( Sheet1[Index] <> 1, [Accumilated Qty] - CALCULATE ( MAX ( [Accumilated Qty] ), FILTER ( Sheet1, Sheet1[Index] = current_ - 1 ), FILTER ( Sheet1, Sheet1[Index] <> 0 ) ), BLANK () )Best,
PaulIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous this looks like exactly what I'm looking for. Ill try it out when i get the chance and report back/accept as soution. Many Thanks!
edit. Actually, this wouldnt work if I had an id of 4 in the same dataset as the id of 5 would it?
Matt_P
This formula is created by looking at the the index column, so it does not matter if your ID is 4 or 5.
Just leave a message for any updates if needed.Best,
