Calculated column to get a customer name

[LBala]

Hi all;
I am working with a power pivot report with a table showing the sales of specific products, if the product was already delivered to the customer and the estimated delivery date. This is the table:

Product	Customer name	Delivered	Estimated delivery date
TV	Susan	Yes	03/15/18
TV	Mary	Yes	04/15/18
TV	John	No	06/15/18
TV	David	No	07/15/18
Computer	Andrew	Yes	04/20/18
Computer	Shannon	Yes	05/20/18
Computer	Anna	No	06/20/18
Computer	Luke	No	07/20/18

<tbody>
</tbody>

I need to create a calculated column showing the name of the next customer to receive the product showing in each row. The table with the calculated column looks like this:

Product	Customer name	Delivered	Estimated delivery date	Next customer to receive the product
TV	Susan	Yes	03/15/18	John
TV	Mary	Yes	04/15/18	John
TV	John	No	06/15/18	John
TV	David	No	07/15/18	John
Computer	Andrew	Yes	04/20/18	Anna
Computer	Shannon	Yes	05/20/18	Anna
Computer	Anna	No	06/20/18	Anna
Computer	Luke	No	07/20/18	Anna

<tbody>
</tbody>

Explaining:

For the product "TV": John and David have not received it yet (Delivered = "No") and John is the next customer to receive it (Estimated delivery date = 06/15/18).

For the product "Computer": Anna and Luke have not received it yet (Delivered = "No") and Anna is the next customer to receive it (Estimated delivery date = 06/20/18).

What DAX formula should I enter in the column "Next customer to receive the product" to retrieve the customer name?

Thanks.

 

[gazpage]

NextCustomer =
[COLOR=#0070ff]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR]
    [COLOR=#0070ff]VALUES[/COLOR][COLOR=#969696] ([/COLOR] Data[Customer name] [COLOR=#969696])[/COLOR],
    [COLOR=#0070ff]TOPN[/COLOR][COLOR=#969696] ([/COLOR]
        [COLOR=#ee7f18]1[/COLOR],
        [COLOR=#0070ff]FILTER[/COLOR][COLOR=#969696] ([/COLOR]
            [COLOR=#0070ff]ALL[/COLOR][COLOR=#969696] ([/COLOR]
                Data[Product],
                Data[Customer name],
                Data[Delivered],
                Data[Estimated Delivery Date]
            [COLOR=#969696])[/COLOR],
            Data[Product] = [COLOR=#0070ff]EARLIER[/COLOR][COLOR=#969696] ([/COLOR] Data[Product] [COLOR=#969696])[/COLOR]
                && Data[Delivered] = [COLOR=#d93124]"No"[/COLOR]
        [COLOR=#969696])[/COLOR],
        Data[Estimated Delivery Date], 
        [COLOR=#0070ff]ASC[/COLOR]
    [COLOR=#969696])[/COLOR]
[COLOR=#969696])[/COLOR]

 

[LBala]

Thanks for the formula gazpage, it works perfectly in the pivot table shown above.


I tried using it in a more complex pivot table and I had some difficulties getting the same result. I did some research and ended up replacing "VALUEs(Data[Customer name])" by "FIRSTNONBLANK(Data[Customer name],1)" in the formula and it worked.


Thanks again for the great help.

 

[gazpage]

Well, that would deal with a tie for the next delivery, assuming you are happy in the case of a tie for the first name alphabetically to be shown.