Search code examples
sortingpowerbidaxlookup

Power BI dataset lookup and sorting

I have a dataset with ordernumber, productionarea and productionstart, data1, data2, data3. The dataset is delivered in a unsorted way.The dataset looks like:

Ordernumber ProductionArea ProductionDate Data1 Data2 Data3
order1 Area1 12/01/2023 5 3 9
order13 Area5 14/01/2023 6 2 4
order11 Area3 19/01/2023 7 9 5
order12 Area1 14/01/2023 8 8 6
order16 Area3 01/01/2023 9 7 7
order13 Area1 13/01/2023 8 6 8

What I want is a solution to add a column (data4) with the result from data2 from the next order. Or a solution to determine the next ordernumber from the same productionarea based on the productiondate. So for order1 I need results from order13 (which is the next one based on the startdate). With a lookup function i'm able to retrieve values from differtent columns, but I need to get the right Next order number first.

So In this case: order1, area1, 12/01/2023 needs a new column with the value from data2 from order13, which is the next order in area1 based on the date.

I've tried to Create a New table (referenced) and sorted it on production date, then use a filter based on the productionunit. But I dont get the results right;

I started with:

VAR CurrentOrderNumber = dataset[ordernumber] 
VAR CurrentArea = dataset[productionarea]
RETURN 
    CALCULATE(MIN('dataset'[ordernumber]), FILTER('dataset','dataset'[ordernumber] > CurrentOrderNumber && ('dataset'[productionarea]=CurrentArea)))

How to do this? The results based on my formula are: Results

It gives the results from order12(area1) instead of order13(area1) , if I sort on productionDate then you can see that order13 is the next in row, not order12.

Result wanted:

Ordernumber ProductionArea ProductionDate Data1 Data2 Data3 Data4
order1 Area1 12/01/2023 5 3 9 6
order13 Area5 14/01/2023 6 2 4
order11 Area3 19/01/2023 7 9 5
order12 Area1 14/01/2023 8 8 6
order16 Area3 01/01/2023 9 7 7 2
order13 Area1 13/01/2023 8 6 8 9

OR

Ordernumber ProductionArea ProductionDate Data1 Data2 Data3 NextOrder
order1 Area1 12/01/2023 5 3 9 order13
order13 Area5 14/01/2023 6 2 4
order11 Area3 19/01/2023 7 9 5
order12 Area1 14/01/2023 8 8 6
order16 Area3 01/01/2023 9 7 7 order11
order13 Area1 13/01/2023 8 6 8 order12
Solution

  • Calculated Column:

    =
VAR ThisProductArea = Table1[ProductionArea]
VAR ThisProductDate = Table1[ProductionDate]
VAR NextProdDate =
    CALCULATE (
        MIN ( Table1[ProductionDate] ),
        FILTER (
            Table1,
            Table1[ProductionArea] = ThisProductArea
                && Table1[ProductionDate] > ThisProductDate
        )
    )
RETURN
    CALCULATE (
        MIN ( Table1[Ordernumber] ),
        FILTER (
            Table1,
            Table1[ProductionArea] = ThisProductArea
                && Table1[ProductionDate] = NextProdDate
        )
    )