HELP! DAX Calculation: Many to many relationship

[jts_]

Hi! I am new to DAX, and am struggling to have a creative way for looking up the most recent service date when customer paid thier payments.

The requirements are as below:

There are 2 tables, Service and Payment (I do not set up any connectivity between them in data model now)

- Service

Service Date	Customr_ID
9/1/2022	101
9/5/2022	103
9/15/2022	101
9/20/2022	102
10/1/2022	103
10/15/2022	102

-Payment

Payment_Date	Customer_ID	Amount($)	Most_Recent_Service_Date
9/1/2022	101	20	9/1/2022
9/9/2022	103	100	9/5/2022
9/21/2022	101	150	9/15/2022
10/1/2022	103	35	10/1/2022
10/11/2022	102	10	9/20/2022
10/18/2022	102	10	10/15/2022

A customer can complete multiple services, and a customer can pay ON or AFTER their service.

Due to the fact that there is a gap between 2 tables, showing payment was paid for which service date. I am looking for writing a dax to calculate the most recent service date that is <= the pyament date per customer.

Column of "Most Recent Service Date" is my hopful output.

Thank you so much in advance! I am looking forward to any possible solutions!

[v-luwang-msft · Accepted solution]

Hi  @jts_ ,

Pls test the below dax to create a new column:

Column = CALCULATE(MAX(Service[Service Date]),FILTER(Service,Service[Service Date]<=Payment[Payment_Date]&&Service[Customr_ID]=Payment[Customer_ID]))

Output result:

Best Regards

Lucien

[jts_]

Hi @v-luwang-msft ,

I appreciate your solution, Lucien! Thanks a lot!!!