Customer Status

[ROCKYDO12]

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 Power BI isn't taking my Customer ID field and it's not working in my calculation. I was hoping someone can look at my demo file attached.

Here is a link to a cleased report with data needed to preform this fuction.  https://drive.google.com/file/d/1xVglVcsvDiHK3t_54cJ9geOrtpw8KdVr/view?usp=sharing

[ROCKYDO12]

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.

[Jihwan_Kim · Accepted solution]

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 ) )

[ROCKYDO12]

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.

[HotChilli]

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)

[ROCKYDO12]

Output result would look like this.

[ROCKYDO12]

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).