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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Customer name[/TD]
[TD]Delivered[/TD]
[TD]Estimated delivery date[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]Susan[/TD]
[TD]Yes[/TD]
[TD]03/15/18[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]Mary[/TD]
[TD]Yes[/TD]
[TD]04/15/18[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]John[/TD]
[TD]No[/TD]
[TD]06/15/18[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]David[/TD]
[TD]No[/TD]
[TD]07/15/18[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Andrew[/TD]
[TD]Yes[/TD]
[TD]04/20/18[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Shannon[/TD]
[TD]Yes[/TD]
[TD]05/20/18[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Anna[/TD]
[TD]No[/TD]
[TD]06/20/18[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Luke[/TD]
[TD]No[/TD]
[TD]07/20/18[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Customer name[/TD]
[TD]Delivered[/TD]
[TD]Estimated delivery date[/TD]
[TD]Next customer to receive the product[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]Susan[/TD]
[TD]Yes[/TD]
[TD]03/15/18[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]Mary[/TD]
[TD]Yes[/TD]
[TD]04/15/18[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]John[/TD]
[TD]No[/TD]
[TD]06/15/18[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD]David[/TD]
[TD]No[/TD]
[TD]07/15/18[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Andrew[/TD]
[TD]Yes[/TD]
[TD]04/20/18[/TD]
[TD]Anna[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Shannon[/TD]
[TD]Yes[/TD]
[TD]05/20/18[/TD]
[TD]Anna[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Anna[/TD]
[TD]No[/TD]
[TD]06/20/18[/TD]
[TD]Anna[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]Luke[/TD]
[TD]No[/TD]
[TD]07/20/18[/TD]
[TD]Anna[/TD]
[/TR]
</tbody>[/TABLE]


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.