Forum Discussion

curtis5611's avatar
curtis5611
Regular Visitor
3 years ago

Calculated column has empty result for some records

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>

Need Help

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      curtis5611
      Regular Visitor
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

        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.