Calculate active years from a customer

Question

I am trying to calculate the amount of active years for customers.&nbsp;We have different tables like "documents transaction date", "customer" and "sales values" .&nbsp;The Tranaction date table is a calender, Customer is all customer information and both are linked to Sales values where are all transactions.I can see the Active years in a matrix per year.Cumulative Distinct Count Until this YTD = TOTALYTD(DISTINCTCOUNT([Related Customer]),'SALES VALUES'[Related Date])But it is not giving meCustomer&nbsp; years active.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp; &nbsp; &nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3Etc.

lc_finance · Accepted Answer

Hi&nbsp;Sjon1791&nbsp;,&nbsp;thank you for the interesting question.You can solve this by adding a calculated column to your customer table.&nbsp;The formula is the following:Years active = 
VAR currentCustomer = CALCULATE(SELECTEDVALUE('CUSTOMERS'[Customer]))
VAR lastYearActive = CALCULATE(MAX('SALES VALUE'[Related Date]),'SALES VALUE'[Customer]=currentCustomer)
VAR firstYearActive = CALCULATE(MIN('SALES VALUE'[Related Date]),'SALES VALUE'[Customer]=currentCustomer)

RETURN YEAR(lastYearActive)- YEAR(firstYearActive)And here is what it looks like:&nbsp;Finally, you can also find a Power BI file with an example here.&nbsp;Let me know if this helps you!&nbsp;Regards,&nbsp;LCInterested in Power BI templates? Check out my blog at www.finance-bi.com

Forum Discussion

Calculate active years from a customer

1 Reply

Related content

Calculating active figures

Calculate Active Customers

calculate cumulative sales for active products

Creating ICMs via Activator

Activity Statistics