Forum Discussion
Solved
Customer Status
Hello, I am getting stuck with implementing this column for creating New, Renewed (Purchased PFY and CFY) and Reactived Custumers (Purchased CFY and any other FY other than PFY). For some reason ...
Hi,
Please check the below picture and the attached pbix file.
I assumed that fiscal year starts from january 1st.
New customers count: = VAR _currentyear = MAX ( 'Calendar'[Year CC] ) VAR _currentcustomerlist = CALCULATETABLE ( VALUES ( 'Customer'[System Record ID] ), 'Customer Base' ) VAR _previouscustomerlist = CALCULATETABLE ( VALUES ( 'Customer'[System Record ID] ), FILTER ( 'Customer Base', RELATED ( 'Calendar'[Year CC] ) < _currentyear ) ) VAR _newcustomerlist = EXCEPT ( _currentcustomerlist, _previouscustomerlist ) RETURN IF ( HASONEVALUE ( 'Calendar'[Year CC] ), COUNTROWS ( _newcustomerlist ) )Renew customers count: = VAR _currentyear = MAX ( 'Calendar'[Year CC] ) VAR _currentcustomerlist = CALCULATETABLE ( VALUES ( 'Customer'[System Record ID] ), 'Customer Base' ) VAR _previouscustomerlist = CALCULATETABLE ( VALUES ( 'Customer Base'[System Record ID] ), FILTER ( 'Customer Base', RELATED ( 'Calendar'[Year CC] ) = _currentyear - 1 ) ) VAR _customerlist = INTERSECT ( _currentcustomerlist, _previouscustomerlist ) RETURN IF ( HASONEVALUE ( 'Calendar'[Year CC] ), COUNTROWS ( _customerlist ) )reactivate customers count: = VAR _currentyear = MAX ( 'Calendar'[Year CC] ) VAR _currentcustomerlist = CALCULATETABLE ( VALUES ( 'Customer Base'[System Record ID] ), 'Customer Base' ) VAR _previouscustomerlist = CALCULATETABLE ( VALUES ( 'Customer Base'[System Record ID] ), FILTER ( 'Customer Base', RELATED ( 'Calendar'[Year CC] ) < _currentyear - 1 ) ) VAR _customerlist = INTERSECT ( _currentcustomerlist, _previouscustomerlist ) RETURN IF ( HASONEVALUE ( 'Calendar'[Year CC] ), COUNTROWS ( _customerlist ) )
Questions: I see a field "System Record ID", is that the Customer ID?
and what is the desired output? (So far, I don't see any records with different dates for the same System Record ID and I don't see any logic to do with Financial Year)
Hey,
Yes, System Record ID is Customer ID. Fiscal Year starts April 1st so I was just planning on adding "03,31" in the calculation. The desired output is to have a column in my data set that would have a customer status applied to it. So either New, Renewed, or re-activated. Then I can pull this field into charts and tables and bring in Count of Customers (System Record ID).