Help to find next location for every row.

[Power_up_high]

Hi,

This is my first post in this forum, I have only been a spectator before and founding answers by looking at questions posted by other members. However, now I am in a situation where I can´t really find a answer to my problem, so hope someone can help me.

I am trying to find the next location for a specific article that is moved around within our production.
The attached document shows a example of one article, sorted by date and the desired outcome for that article (see column D). As you can see sometimes the location is not changed so the next location is still the same as the current location. When there is no newer transaction I wish the outcome to be 0.

This seems simple, however my orginal data table contains several thousand articles and is not sorted by either date or article.
I have tried several different solutions trying to use CALCULATE, MIN AND FILTER. However I seem to be doing something wrong each time.

Hope someone can help with this problem. Please ask if there is any information missing. 

Thanks. 

Edit: Seems that the csv file is not supported? Here is the table: 

DATE	Article	Location	(Desired outcome): Next location
2022-08-26	A	8579	0
2018-08-25	A	8522	8579
2018-07-21	A	8522	8522
2018-06-16	A	8522	8522
2018-06-07	A	-1	8522
2018-05-19	A	8522	-1
2018-04-21	A	8522	8522
2018-04-21	A	8522	8522
2018-04-16	A	8522	8522
2018-03-24	A	8522	8522
2018-03-24	A	8522	8522
2018-02-24	A	8522	8522
2018-02-24	A	8522	8522
2018-01-29	A	8522	8522
2018-01-20	A	8522	8522
2017-12-09	A	8522	8522
2017-12-09	A	8522	8522
2017-11-18	A	8522	8522
2017-11-18	A	8522	8522
2017-11-09	A	8693	8522
2017-10-21	A	8743	8693
2017-10-20	A	8743	8743
2017-10-20	A	8743	8743
2017-10-20	A	8522	8743
2017-10-20	A	8522	8522

[v-jianboli-msft]

Hi @Power_up_high ,

In your orginal table  article A has two unique locations (8522 and 8743) for the date 2017-10-20. 

Your orginal data table contains several thousand articles and is not sorted by either date or article. So how to determine which one is the nex location?

Best Regards,

Jianbo Li

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

[Power_up_high]

Hi @v-jianboli-msft,

Thanks for taking your time to answer. With your example I see that I have thought about this the wrong way, It is not possible to determine this based on the values given in the example table. For example if there was with the date 2017-10-19 it wouldn't know which location to pick up from the rows with the date 2017-10-20. 

Based on your input I have asked for other data internally and I have been given a new column called serial_number. This column is a number for a transaction (a row) given to a row based on when the row was created, so it gives us a better order instead of date. There is no dupliacte serial numbers, however the table is not ordered by the serial_number (see new example for article A below). 

I tried to change @serpiva64 code suggestion so it looks at serial number instead of date but still returns the wrong location.

So now my table looks like this:

Serial_number               DATE                                                                               Article                            Location                              Next location (desired                                                                                                                                                                                                                     outcome) 29 2022-11-12 A -1 0 28 2022-11-12 B 8579 0 4 2017-10-20 B 8522 8579 3 2017-10-20 B 8522 8522 2 2017-10-20 B 8522 8522 27 2022-11-12 A 8579 -1 26 2018-08-25 A 8522 8579 25 2018-07-21 A 8522 8522 24 2018-06-16 A 8522 8522 23 2018-06-07 A -1 8522 22 2018-05-19 A 8522 -1 21 2018-04-21 A 8522 8522 20 2018-04-21 A 8522 8522 19 2018-04-16 A 8522 8522 18 2018-03-24 A 8522 8522 17 2018-03-24 A 8522 8522 16 2018-02-24 A 8522 8522 15 2018-02-24 A 8522 8522 14 2018-01-29 A 8522 8522 13 2018-01-20 A 8522 8522 12 2017-12-09 A 8522 8522 11 2017-12-09 A 8522 8522 10 2017-11-18 A 8522 8522 9 2017-11-18 A 8522 8522 8 2017-11-09 A 8693 8522 7 2017-10-21 A 8743 8693 6 2017-10-20 A 8743 8743 5 2017-10-20 A 8743 8743 4 2017-10-20 A 8522 8743 1 2017-10-20 A 8522 8522

Based on this, I am hoping that the next location for a article can be found based on the article name and the first serial number for that article after current row. So for example the next location for article A with serial number 27 should be -1 since this the locations for serial number 29, which is the first serial number for article A after serial number 27. 

Long post, but I hope this makes more sense with a new column. @v-jianboli-msft or @serpiva64 do you have any suggestions on a solution for this?

Thanks!

[Ashish_Mathur]

Hi,

This calculated column formula works

Next Location = COALESCE(LOOKUPVALUE(Data[Location],Data[Serial_number],CALCULATE(MIN(Data[Serial_number]),FILTER(data,Data[Article]=EARLIER(Data[Article])&&Data[Serial_number]>EARLIER(Data[Serial_number]))),Data[Article],Data[Article]),0)

Hope this helps.

[v-jianboli-msft · Accepted solution]

Hi @Power_up_high ,

If you need a calculated column, please try:

Next location =
VAR _a =
    MINX (
        FILTER (
            'Table',
            [Article] = EARLIER ( 'Table'[Article] )
                && [Serial_number] > EARLIER ( 'Table'[Serial_number] )
        ),
        [Serial_number]
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[Location] ),
        FILTER (
            'Table',
            [Article] = EARLIER ( 'Table'[Article] )
                && [Serial_number] = _a
        )
    )
RETURN
    _b + 0

Output:

If you need a measure, please try:

Measure =
VAR _a =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            [Article] = MAX ( 'Table'[Article] )
                && [Serial_number] > MAX ( 'Table'[Serial_number] )
        ),
        [Serial_number]
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[Location] ),
        FILTER (
            ALL ( 'Table' ),
            [Article] = MAX ( 'Table'[Article] )
                && [Serial_number] = _a
        )
    )
RETURN
    _b + 0

Output:

Best Regards,

Jianbo Li

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

[Power_up_high]

Hi @Ashish_Mathur & @v-jianboli-msft 

Thanks for taking your time and giving suggestions on two different solutions.
@Ashish_Mathur : I tried the solution you provided, however when Power BI tries creating the column I receive the error that there is not enough memory to complete the action. The reason is probably because the table is very large, almost 27 million rows.

@v-jianboli-msft : I also tried your suggestions, and both the measure and the calculated columned worked. The measure seems to work better with regards to performance, the calculated column took a long time before showing a result. 
However I would like to have a calculated column. Do you have any suggestion on how to increase performance for your calculated column solution? I only have two other columns in the table I provided and that data are not relevant for this. Could I perhaps remove these in the calculation in some way? Or do you have any other suggestions?

Thanks for helping me to find a solution. 

[serpiva64]

Hi,

there isn't an attached file

[Power_up_high]

Hi,

Sorry, I had some problems attaching the document. I have edited my first post showing the table. 

Thanks!

[serpiva64]

Hi,

You can try 

Column = LOOKUPVALUE(Locations[Location],Locations[DATE], OFFSET(1,ALLSELECTED(Locations[DATE]),ORDERBY(Locations[DATE]),,))

If this post is useful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[Power_up_high]

Hi,
Thanks for your response. The suggested solutions returns #Error on all rows when creating a new column and the message : A table with several values were supplied, where a single value was expected. 
Could the problem be because of some formatting? The locations and article column is in

decimal number. 

Thanks!

[serpiva64]

Please can you post your new column

[Power_up_high]

Hi,

I copied your code. But here it is from my side: 

Next location = LOOKUPVALUE(LOCATIONS[LOCATION],LOCATIONS[DATE], OFFSET(1,ALLSELECTED(LOCATIONS[DATE]),ORDERBY(LOCATIONS[DATE]),,)).

Thanks!

[serpiva64]

Hi,

i get this error if i use that formula in a measure insted of a calculated column

[Power_up_high]

Hi again,

Thanks for taking your time to response and helping me out.
I have tried your code with the table example I provided in my orginal post (just as you did) and it worked. 

However, it is still not working in my real table. Could one of the reasons for the error be:

- That the same article can have the same dates but different locations? For example, in my orginal table  article A has two unique locations (8522 and 8743) for the date 2017-10-20. 
- That different articles have the same dates and same or different locations?
- How does the code know which article to look at? As I wrote in my orginal post, the table consists of several thousand articles and not one article as I the example I provided.

I have tried myself to also make changes to your suggestions without success. For example I used the function: FIRSTNONBLANK instead of lookupvalue, but I only received the same value as the current row value for location.

Thanks for supporting me and hope there is someone out there who have a solution to this problem.