New customer with first invoice date and amount

[BIEnthu]

Hi

I am trying to create report with customer name their first invoice date and invoice amount. I have a customer table and invoice date and invoice tables.

I am using DAX 

Min_Date = CALCULATE(MIN('Invoice'[Invoice Date]), ALLEXCEPT('CustomerTable', 'CustomerTable'[Customer Name]))

I am getting multiple records for the same customer with each invoice date. how can i get only the first customer row. 

I tried multiple things like getting the first appearance and also using filter on TOPN, it is not helping.

Any recommendations is much appreciated. Thank you for your help in advance

[sangeethray_92]

@BIEnthu 

If tou just want to use measure. Try this. 

First_Invoice_Date =
CALCULATE(
MIN('Invoice'[Invoice Date]),
ALLEXCEPT('Invoice', 'Invoice'[customer id])
)

First_Invoice_Amount =
VAR FirstDate = CALCULATE(
MIN('Invoice'[Invoice Date]),
ALLEXCEPT('Invoice', 'Invoice'[customer id])
)
RETURN
CALCULATE(
SUM('Invoice'[Invoice Amount]),
'Invoice'[Invoice Date] = FirstDate
)

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

[sangeethray_92]

create a calculated column in the invoice table. 

first_date =
VAR A = CALCULATE(
MIN('Invoice'[Invoice Date]),
ALLEXCEPT('Invoice', 'Invoice'[customer id])
)
RETURN
IF(A = 'Invoice'[Invoice Date], 1, 0)

Use rhis column in the visual level filter and filter for 1. 

this post helps, then please give us Kudos and consider accepting it as a solution to help other members find it more quickly.

[BIEnthu]

Thank you bhanu_gautam and Yulia Xu for your inputs! 

I have 3 tables under this scenario. This is MS Fabric and i don't have option to create new/custom column

Invoice Amount:

Net Amount

Invoice Date

Invoice Date	creation date

Customer

Name	Address	zip	Rep
Life Financials	20 Lincoln Ave	51231	Nicole
State Electric	500 Group Dr	62312	Jane
Life Financials	20 Lincoln Ave	51231	Nicole

[v-xuxinyi-msft]

Hi @BIEnthu 

I would like to apologize for the belated reply. 

I didn't create a new column, I used your original DAX, and then I tried to build the relationship between the tables as shown in the screenshot to get the correct desired result.

Based on the information you've given so far, it doesn't seem like you have the same columns between the three tables? If this is not your complete data, you can try to build a relationship to see if it will meet your needs.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sangeethray_92]

Are you trying to display the first record of the customer ?  @BIEnthu 

[BIEnthu]

Yes, looking to display the first first record of each customer. Thanks!

[v-xuxinyi-msft]

Hi @BIEnthu 

Thanks for the reply from bhanu_gautam .

BIEnthu , I have created a simple example data for testing, according to the test if there is a relationship between the two tables then every customer can get the earliest invoice date correctly, is there a relationship between your two tables? If my data structure is different from yours, could you please provide the sample data about both tables so that we can help you better? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance.

My sample:

If there is no relationship between the two tables, the result will not be what you need.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@BIEnthu Create a calculated column for the first invoice date:

FirstInvoiceDate =
CALCULATE(
MIN('Invoice'[Invoice Date]),
ALLEXCEPT('CustomerTable', 'CustomerTable'[Customer Name])
)

Create a calculated column for the first invoice amount:

FirstInvoiceAmount =
CALCULATE(
FIRSTNONBLANK('Invoice'[Invoice Amount], 1),
FILTER(
'Invoice',
'Invoice'[Invoice Date] = EARLIER('CustomerTable'[FirstInvoiceDate])
)
)

Create a summary table to display the results:

CustomerFirstInvoiceSummary =
SUMMARIZE(
'CustomerTable',
'CustomerTable'[Customer Name],
'CustomerTable'[FirstInvoiceDate],
'CustomerTable'[FirstInvoiceAmount]
)