CODEWITHSUNDEEP
excelvbadaxpowerquerypowerpivot
Phil T
Phil T

Reputation: 87

Excel Power Pivot: Match next non-blank item after date match for customer across two tables using DAX

I need to create a calculated column in Table_Order that would find the next matching, non-blank Customer Status on or after the current Order Date for the current row Customer #.

The tricky part is when there is no matching Customer Status in Table_Ship for a Customer # on a particular Order Date (please see the orange highlights). The match then needs to be on the next matching Ship Date for the Customer # where the Customer Status is not blank.

Please could somebody help me and show me the DAX?

I've spent a long time Googling and have gotten nowhere. I feel like this should be easy!

Also please bear in mind that these tables are massively simplified versions of my actual work, and any suggestion to reorganise the tables or start from scratch won't help me, I need some DAX please!

Thanks in advance!

Phil.

enter image description here

Upvotes: 1

Views: 141

Answers (1)

Angelo Canepa
Angelo Canepa

Reputation: 1781

I understand that you have large datasets, but I would recommend you create a FactCustomer table. Here there are the steps I followed with the data you provided.

  1. Create a FactCustomer table
FactCustomer =
    DISTINCT ( Table_Ship[Customer #] )
  1. Create a relationship between Table_Ship and FactCustomer and Table_Order and FactCustomer, using Customer # as key.

enter image description here

  1. Create a calculation in Table_Order to find customer status. The calculation makes a lookup (similar to VLOOKUP in excel) to find customer with the same date. This would retrieve the blank for Customer #3, so is neccesary to create a second calculation that would scan Table_Ship for the minimum date without blanks. The final step is using an IF statement to treat blank in the first function, when they are blank, the second formula is used.
Customer Status = 
var find_customer = LOOKUPVALUE(Table_Ship[Customer Status],Table_Ship[Ship_Date],[Order Date],Table_Ship[Customer #],[Customer #])
var last_non_blank_date = CALCULATE(min(Table_Ship[Ship_Date]),Table_Ship[Customer Status]<>BLANK())
var find_non_blank = CALCULATE(min(Table_Ship[Customer Status]),filter(Table_Ship,Table_Ship[Ship_Date]=last_non_blank_date))

return if(ISBLANK(find_customer),find_non_blank,find_customer)

The result I get:

enter image description here

Upvotes: 1

Related Questions