cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Isidro
Helper IV
Helper IV

Calculate the seniority of a costumer

Hello,

 

I have two tables:

The first is a table with unique values with the codes of all customers.

The second is a table with billing for several years. In one column is the customer code, in another column is the invoice number and in another column is the invoice date.

How can you calculate in the first table the seniority of a customer with the date of the invoice?

Thank you very much
and best regards.

2 ACCEPTED SOLUTIONS
malagari
Resolver IV
Resolver IV

By seniority, I'm guessing you want the age of the customer based on their earliest invoice, as well as the earliest invoice date?

 

Assuming that you have a relationship set up between your two tables based on the CustomerID, you can do something like this:

 

Oldest Invoice = CALCULATE( MIN(Table2[InvoiceDate]) )

Customer Age = CALCULATE( DATEDIFF([Oldest Invoice], TODAY(), DAY) )

This will give you a calculated column called [Oldest Invoice] that will grab the MIN InvoiceDate for each Customer.  Then, you can either create a calculated column or measure called Customer Age that calculates the number of days between TODAY and their earliest invoice.

 

Dan Malagari
Consultant at Headspring

View solution in original post

@Isidro,


Right click on your first table and select "New measure" to apply the following DAX.

Oldest Invoice = MIN('Table 2'[invoice date])
Age = DATEDIFF('Table 1'[Oldest Invoice],TODAY(),DAY)

1.JPG

If the above dax don't return your expected result, please share sample data of your tables and post desired result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
malagari
Resolver IV
Resolver IV

By seniority, I'm guessing you want the age of the customer based on their earliest invoice, as well as the earliest invoice date?

 

Assuming that you have a relationship set up between your two tables based on the CustomerID, you can do something like this:

 

Oldest Invoice = CALCULATE( MIN(Table2[InvoiceDate]) )

Customer Age = CALCULATE( DATEDIFF([Oldest Invoice], TODAY(), DAY) )

This will give you a calculated column called [Oldest Invoice] that will grab the MIN InvoiceDate for each Customer.  Then, you can either create a calculated column or measure called Customer Age that calculates the number of days between TODAY and their earliest invoice.

 

Dan Malagari
Consultant at Headspring

View solution in original post

Hello @malagari,

 

Creating the calculated column "Oldest Invoice" gives me the following mistake: a circular dependency was detected.

 

Thank you very much for your answer.

 

Best regards.

@Isidro,


Right click on your first table and select "New measure" to apply the following DAX.

Oldest Invoice = MIN('Table 2'[invoice date])
Age = DATEDIFF('Table 1'[Oldest Invoice],TODAY(),DAY)

1.JPG

If the above dax don't return your expected result, please share sample data of your tables and post desired result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hello Lydia,

 

@v-yuezhe-msft

 

I have tested the measurements and they work correctly.

 

Thank you very much and best regards.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.