cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 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 5
Super User IV

## Re: Grouping / Aggregation

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/3395...

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helper I

## Re: Grouping / Aggregation

Thanks, looked at EARLIER but im finding it difficult to apply... hence the post. 😁

Microsoft

## Re: Grouping / Aggregation

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,
Paul

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

## Re: Grouping / Aggregation

@V-pazhen-msft 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?

Microsoft

## Re: Grouping / Aggregation

@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.

Best,

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors