powerpivot - DAX measure for first mininum date, second mininum date, etc

Question

I'm trying to make a Pivot Table with multiple fields to show different delivery dates and the quantity of product that will be delivered.

My dataset looks like this:

PRODUCT    DELIVERY DATE        PENDING_QUANTITY       OTHER COLUMNS
1           08/11/2020               15
2           25/02/2021               300
4           19/01/2021               150 
1           15/12/2020               30
1           10/03/2021               200 
2           15/03/2021               100  

And I'd like to pivot it like this:

PRODUCT  OTHER COLUMNS  1ST DELIVERY  1ST QUANTITY  2ND DELIVERY  2ND QUANTITY   3RD DELIVERY    ETC
1                        08/11/2020       15        15/12/2020       30          10/03/2021
2                        25/02/2021       300       15/03/2021       100
4                        19/01/2021       150

With this calculation I managed to bring the lowest date:

1STDATE:=CALCULATE(MINX(Consulta2;Consulta2[Delivery_Date]);TablaComps)

But I don't know how to bring the next dates. I tried this code, but it's not working:

2NDATE:=CALCULATE(MINX(FILTER(Consulta2;Consulta2[Delivery_Date]>[1STDATE]);consulta2[Delivery_Date]);tablacomps)

EDIT: Ok, I did it, but the measure doesn't look clean at all.

7th DELIVERY:=VAR mindate =
        CALCULATE ( min ( Consulta2[Delivery_Date] ); tablacomps )
        VAR secondmindate =
    CALCULATE (
            MIN (Consulta2[Delivery_Date]);
            FILTER ( ALL ( CONSULTA2 ); Consulta2[Delivery_Date] > mindate );TablaComps)
        VAR thirdmindate =
        CALCULATE (
            MIN (Consulta2[Delivery_Date]);
            FILTER ( ALL ( CONSULTA2 ); Consulta2[Delivery_Date] > secondmindate && Consulta2[Delivery_Date] > mindate);TablaComps)
        VAR forthmindate =          
        CALCULATE (
            MIN (Consulta2[Delivery_Date]);
            FILTER ( ALL ( CONSULTA2 ); Consulta2[Delivery_Date] > thirdmindate 
                                    && Consulta2[Delivery_Date] > secondmindate
                                    && Consulta2[Delivery_Date] > mindate);TablaComps)
        VAR fifthmindate =          
        CALCULATE (
            MIN (Consulta2[Delivery_Date]);
            FILTER ( ALL ( CONSULTA2 ); Consulta2[Delivery_Date] > thirdmindate 
                                    && Consulta2[Delivery_Date] > secondmindate
                                    && Consulta2[Delivery_Date] > forthmindate
                                    && Consulta2[Delivery_Date] > mindate);TablaComps)
    VAR sixthmindate = 
            CALCULATE (
            MIN (Consulta2[Delivery_Date]);
            FILTER ( ALL ( CONSULTA2 ); Consulta2[Delivery_Date] > thirdmindate 
                                    && Consulta2[Delivery_Date] > secondmindate
                                    && Consulta2[Delivery_Date] > forthmindate
                                    && Consulta2[Delivery_Date] > fifthmindate
                                    && Consulta2[Delivery_Date] > mindate);TablaComps)

 RETURN
        CALCULATE (
            MIN (Consulta2[Delivery_Date]);
            FILTER ( ALL ( CONSULTA2 ); Consulta2[Delivery_Date] > thirdmindate 
                                    && Consulta2[Delivery_Date] > secondmindate
                                    && Consulta2[Delivery_Date] > forthmindate
                                    && Consulta2[Delivery_Date] > fifthmindate
                                    && Consulta2[Delivery_Date] > sixthmindate
                                    && Consulta2[Delivery_Date] > mindate);TablaComps)

I had to create one measure for each delivery on the pivottable, and the same to connect the pending_quantity with the dates. Is there any way to do it cleaner?

Any help will be appreciated!

question from:https://stackoverflow.com/questions/66045705/dax-measure-for-first-mininum-date-second-mininum-date-etc

Answer 1

Waitting for answers