Calculate next value of a column with same characteristics

[AGo]

Hi,

I'm trying to create a new column (I'll name it "next_date") in which I'd like to have the next date associated to the same product.

For example:

-In line 5 there would be 1 - 8/02/2016 - 10/02/2016

-In line 3 and 8 there would be 3 - 8/02/2016 - 8/02/2016 because for each one of the two lines there's another line with the same date.

-If there's no next_date like for line 7 the cell have to remain blank.

I started typing a dax formula with CALCULATE function but I can't find a way to properly filter on the same column (date) having a condition of equality based on another column (product ID).

[v-haibl-msft]

@AGo

You can first add an index column in Query Editor as below.

Then close and apply Query Editor, create a calculate Column with following formula.

Column = 
CALCULATE (
    MAX ( Table1[date] ),
    FILTER (
        ALL ( Table1 ),
        EARLIER ( Table1[product ID] ) = Table1[product ID]
            && EARLIER ( Table1[date] ) <= Table1[date]
            && EARLIER ( Table1[Index] ) <> Table1[Index]
    )
)

Best Regards,

Herbert

[AGo]

Thank you, I think your solution is logically perfect, but the problem is that I tested it on a 400K rows table and it crashes after reaching 13GB RAM, the target is to have it working on a 2 million rows table.

Maybe we can rewrite it as an M (Power Query code) and execute it before the dataset creation?

[v-haibl-msft]

@AGo

How about using following updated DAX formula?

Next_Date = 
CALCULATE (
    MIN ( Table1[Date] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[product ID] ),
        EARLIER ( Table1[date] ) <= Table1[date]
            && EARLIER ( Table1[Index] ) <> Table1[Index]
    )
)

Best Regards,

Herbert