cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 KeyKey(Product+country)Category Short TextReceipt Quantity / Requirements QuantityProduct NumberCountryBatch #Expire dateSales Forecast Until Expire  DateTotal  StockavailableAccumulated Stock of F ColSales Forecast between Two Expire DateScrap Volume
between 2 expire dates
Accumulated Scrap Volume
18134 PT A1513418134 PTStock2918134PTA1513419-08-20170,0088152902929
18134 PT A2432718134 PTStock3818134PTA2432721-10-20170,0088156703867
18134 PT A3730018134 PTStock4018134PTA3730001-12-20170,008815107040107
18134 PT A6518018134 PTStock4.82818134PTA6518021-12-2018-3450,0088154935-345013781485
18134 PT A7298418134 PTStock3.88018134PTA7298403-03-2019-6014,0088158815-256413162801

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
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:

6.JPG

 

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.

View solution in original post

8 REPLIES 8
Super User I
Super User I

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

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

 

image.png

 

 



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,

 

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

Anonymous
Not applicable

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

Here is A bit mroe info using your steps:

 

test1.jpg

 

 

Community Support
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:

6.JPG

 

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.

View solution in original post

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

 

Remind.jpg

Anonymous
Not applicable

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

Hej PattemManohar, @PattemManohar

 

Thanks for your help anyway.

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

 

Thanks for your help anyway.

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!

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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