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

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

Accepted Solutions
malagari Member
Member

Re: Calculate the seniority of a costumer

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
Moderator v-yuezhe-msft
Moderator

Re: Calculate the seniority of a costumer

@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.
4 REPLIES 4
malagari Member
Member

Re: Calculate the seniority of a costumer

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
Isidro Member
Member

Re: Calculate the seniority of a costumer

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.

Moderator v-yuezhe-msft
Moderator

Re: Calculate the seniority of a costumer

@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.
Isidro Member
Member

Re: Calculate the seniority of a costumer

Hello Lydia,

 

@v-yuezhe-msft

 

I have tested the measurements and they work correctly.

 

Thank you very much and best regards.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 134 members 1,661 guests
Please welcome our newest community members: