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 ) )
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 ) )
Hey,
thank you for this. I was hoping to make it work to have a column added with the different statuses instead of apply just a count calculation. Also fiscal year starts April 1, sorry should of clarified.