Calculate the seniority of a costumer

[Isidro]

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.

[malagari · Accepted solution]

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.

[malagari · Accepted solution]

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.

[Isidro]

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.

[v-yuezhe-msft · Accepted solution]

@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)



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

Regards,
Lydia

[Isidro]

Hello Lydia,

@v-yuezhe-msft

I have tested the measurements and they work correctly.

Thank you very much and best regards.