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(C...
Need Help
johnt75's avatar
johnt75
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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.