cancel
Showing results for
Did you mean:
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
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
Microsoft

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

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.
4 REPLIES 4
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
Helper IV

Hello @malagari,

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

Best regards.

Microsoft

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

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.
Helper IV

Hello Lydia,

@v-yuezhe-msft

I have tested the measurements and they work correctly.

Thank you very much and best regards.

Announcements