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

[Anonymous]

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 Volume between 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?

Many Thanks in advance!

Mvh

Yuqi

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Hej Lin

I figured out the other formulars.

however your Rank is the Key.

Many thanks for help!

[Anonymous]

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
Many thanks in advance!

[PattemManohar]

@Anonymous Please try below steps:

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)

[Anonymous]

Hej PattemManohar, @PattemManohar

Thanks for your help anyway.

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

Best regards,

Yuqi

[Anonymous]

Hej PattemManohar, @PattemManohar

Thanks for your help anyway.

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

Best regards,

Yuqi

[Anonymous]

Here is A bit mroe info using your steps:

[Anonymous]

Hej PattemManohar,

Many thanks for your fast reply!

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