Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
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
)
|
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.
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 |
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.
Yes, looking to display the first first record of each customer. Thanks!
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.
@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]
)
Proud to be a Super User! |
|
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
28 | |
15 | |
11 | |
11 | |
9 |
User | Count |
---|---|
24 | |
15 | |
15 | |
14 | |
13 |