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 ) )
Please disregard! Thank you very much for your help and efforts. I was using the wrong field!!!!!! Wow this would of saved me a bunch of time if only I knew.