Calculated column has empty result for some records

[curtis5611]

I have added a Calculated Column in a Customers Table with this DAX formula:

= VAR CustomerPurchase = FILTER('Sales', 'Sales'[CustomerNo] = CurrentCustomerID)

   VAR SortedPurchase = ADDCOLUMNS(CustomerPurchase, "PurchaseRank", RANKX(CustomerPurchase,         'Sales'[PurchaseDate], , DESC) )

   VAR SecondLastPurchaseRank = MAXX(FILTER(SortedPurchase, [PurchaseRank] = 2), [PurchaseRank])

   RETURN

        MINX(FILTER(SortedPurchase, [PurchaseRank] = SecondLastPurchaseRank), 'Sales'[PurchaseDate])

After executing this, most of the records in this Customers Table has results for this Calculated Column, but there are few records having empty cell. I have confirmed that there are Purchase data for these customers. How can I resolve this>

[johnt75]

Try

Second last purchase date =
VAR AllPurchases =
    CALCULATETABLE ( VALUES ( Sales[PurchaseDate] ) )
VAR MostRecentPurchases =
    TOPN ( 2, AllPurchases, Sales[PurchaseDate], DESC )
VAR Result =
    MINX ( MostRecentPurchases, Sales[PurchaseDate] )
RETURN
    Result

This assumes that you have a relationship from Customer to Sales.

[curtis5611]

Hi @johnt75 ,

Thanks for your reply. When I tried using your approach, I received this error: 

The relationship between the Customer and Sales Tables is through the "CustomerNo" field in both tables.

[johnt75]

I've never come across that error before, but it sounds like a data quality issue. I would have a look at the customer no field in both tables and make sure that they match up correctly.