cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Help wanted: Calculating based on value of former/Above Row(s)

Hi PowerBI masters,

Here comes a challenge , which I searched a lot of place for help but in vain.

I am trying to calculate a column , where the it is based on the values of former /Above Row(s).

an example is : (How could we Calcluate the column with Red header?)

 Batch Key Key(Product+country) Category Short Text Receipt Quantity / Requirements Quantity Product Number Country Batch # Expire date Sales Forecast Until Expire  Date Total  Stockavailable Accumulated Stock of F Col Sales Forecast between Two Expire Date Scrap Volumebetween 2 expire dates Accumulated Scrap Volume 18134 PT A15134 18134 PT Stock 29 18134 PT A15134 19-08-2017 0,00 8815 29 0 29 29 18134 PT A24327 18134 PT Stock 38 18134 PT A24327 21-10-2017 0,00 8815 67 0 38 67 18134 PT A37300 18134 PT Stock 40 18134 PT A37300 01-12-2017 0,00 8815 107 0 40 107 18134 PT A65180 18134 PT Stock 4.828 18134 PT A65180 21-12-2018 -3450,00 8815 4935 -3450 1378 1485 18134 PT A72984 18134 PT Stock 3.880 18134 PT A72984 03-03-2019 -6014,00 8815 8815 -2564 1316 2801

Attached in the link is the excel with Formuler,

https://www.dropbox.com/s/acfaojhlrv1sc83/example%20sheet.xlsm?dl=0

Could any Masters help with it?

Mvh

Yuqi

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Help wanted: Calculating based on value of former/Above Row(s)

hi, @Anonymous

I met a few times before this problem, It is because of the same Expire date for one Product+country has multiple Batch #.

So you could use this formula to add a rank column

```Rank = RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),
RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),
Test105PrevRow[Expire date],
,
ASC
)
+ DIVIDE (
RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),
Test105PrevRow[Batch #],
,
ASC
),
(
COUNTROWS (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
)
)
+ 1
)
),
,
ASC
)```

Then use other formulas to continue to calculate

Result:

By the way, for your special case.

you could use this simple formula:

```simple rank = RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),Test105PrevRow[Batch Key],,ASC)```

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Super User I

## Re: Help wanted: Calculating based on value of former/Above Row(s)

Step1: Create an Index field as below

`Rnk = RANKX(FILTER(Test105PrevRow,Test105PrevRow[KeyProdCntry]=EARLIER(Test105PrevRow[KeyProdCntry])),Test105PrevRow[ExpireDate],Test105PrevRow[ExpireDate],ASC)`

Step2: Create as a "New Column"

```ScrapVolume =
VAR _Curr = Test105PrevRow[Accumulated Scrap Volume]
VAR _Prev = CALCULATE(MIN(Test105PrevRow[Accumulated Scrap Volume]),FILTER(ALL(Test105PrevRow),Test105PrevRow[KeyProdCntry]=EARLIER(Test105PrevRow[KeyProdCntry]) && Test105PrevRow[Rnk] = EARLIER(Test105PrevRow[Rnk])-1))
RETURN _Curr-_Prev```

Step3 : Create as a "New Column"

```SalesForeCastb/wExprieDates =
VAR _Curr = Test105PrevRow[SalesForecast]
VAR _Prev = CALCULATE(SUM(Test105PrevRow[SalesForecast]),FILTER(ALL(Test105PrevRow),Test105PrevRow[KeyProdCntry]=EARLIER(Test105PrevRow[KeyProdCntry]) && Test105PrevRow[Rnk] = EARLIER(Test105PrevRow[Rnk])-1))
RETURN _Curr-_Prev```

The result will look like... (Last three columns are the new additional fields)

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Anonymous
Not applicable

## Re: Help wanted: Calculating based on value of former/Above Row(s)

Hej PattemManohar,

I tried your steps , but It does not work.

If I understood it rightly, you are using my last column to calculate the Scrapped Vol.

Let me present in another way, Could you kindly help calculate all last 4 columns in my examples?

I have put the 3 examples and their detail exaplaination in the following link.

https://www.dropbox.com/s/ukcwwhkcmaz19d9/Test%203%20examples.xlsx?dl=0

Hope that you could help through.

Many thanks for your time again!

Best regards,

Yuqi

Anonymous
Not applicable

## Re: Help wanted: Calculating based on value of former/Above Row(s)

Here is A bit mroe info using your steps:

Community Support

## Re: Help wanted: Calculating based on value of former/Above Row(s)

hi, @Anonymous

I met a few times before this problem, It is because of the same Expire date for one Product+country has multiple Batch #.

So you could use this formula to add a rank column

```Rank = RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),
RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),
Test105PrevRow[Expire date],
,
ASC
)
+ DIVIDE (
RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),
Test105PrevRow[Batch #],
,
ASC
),
(
COUNTROWS (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
)
)
+ 1
)
),
,
ASC
)```

Then use other formulas to continue to calculate

Result:

By the way, for your special case.

you could use this simple formula:

```simple rank = RANKX (
FILTER (
Test105PrevRow,
Test105PrevRow[Key(Product+country)]
= EARLIER ( Test105PrevRow[Key(Product+country)] )
),Test105PrevRow[Batch Key],,ASC)```

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

## Re: Help wanted: Calculating based on value of former/Above Row(s)

Hej Lin,  @v-lili6-msft

Firstly thanks for your great Help!
So far we have the right Rnk !

However the other fomulars of calculation for Scrapped volumn is not right ( or maybe I understood it wrong).
it does not solve the follwoing senarios.
1. It is now using my "Accumulated Scrap Volumn" to calculate my "Scrap Volumn between two expire dates". The Logic should be opposit.
2.BlockedStock should go Scrpped Volumn  directly. ( I can handle it by seperating them into a seperate table, however hope there could be a way to handle that  in a same table.

Since I am new in PowerBI(2 months),
Could you Kindly help me do a detail pbx based on the 3 examples?
https://www.dropbox.com/s/ukcwwhkcmaz19d9/Test%203%20examples.xlsx?dl=0

Anonymous
Not applicable

## Re: Help wanted: Calculating based on value of former/Above Row(s)

Hej PattemManohar, @PattemManohar

Your suggestion was a quite good start and very nearby the answer!

Best regards,

Yuqi

Anonymous
Not applicable

## Re: Help wanted: Calculating based on value of former/Above Row(s)

Hej PattemManohar, @PattemManohar

Your suggestion was a quite good start and very nearby the answer!

Best regards,

Yuqi

Anonymous
Not applicable

## Re: Help wanted: Calculating based on value of former/Above Row(s)

Hej Lin

I figured out the other formulars.

however your Rank is the Key.

Many thanks for help!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors