Calculate Resurrect Customer Count by Product for Line Chart

[etane]

Hello.  

I am trying to create a line chart for two products such as the one below. For this chart, it should show resurrect customer count by month in which they made their first order this year:

Definition for resurrect customer by product is the following criteria:
1) Product = Product A or Product B
2) Units Purchased during CY > 0

3) Units Purchased during PY = 0

4) Units Purchased previous to PY > 0

Link to my WIP file: Link

Thanks!

[FBergamaschi]

Yes,please send a direct message so I do not lose the tread

[etane]

@FBergamaschi just needed a minor tweak for it to work.  Thanks again!

[v-sgandrathi]

Hi,

Thank you @FBergamaschi for your response to the query.

@etane, has your issue been resolved?
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[FBergamaschi · Accepted solution]

Resurr Cust Count =

VAR MaxVisibleDate = MAX ( 'Calendar'[Date] )

VAR MinVisibleDate = MIN ( 'Calendar'[Date] )

VAR CurrentYear = SELECTEDVALUE( 'Calendar'[Year#] )

VAR Customers = CALCULATETABLE( VALUES ( 'Customer Table'[Customer Name] ), REMOVEFILTERS( 'Calendar' ), 'Calendar'[Year#] = CurrentYear)

VAR CustomersAndFirstPurchaseCY =

CALCULATETABLE(

ADDCOLUMNS(

    Customers,

    "FirstPurchaseDateCY", CALCULATE( MIN ( Ord_Tbl[Order Date] ), REMOVEFILTERS( 'Calendar' ), 'Calendar'[Year#] = CurrentYear ),

    "QtyCY", CALCULATE( SUM ( Ord_Tbl[Units] ), REMOVEFILTERS( 'Calendar' ), 'Calendar'[Year#] = CurrentYear ),

    "QtyPY", CALCULATE( SUM ( Ord_Tbl[Units] ), REMOVEFILTERS( 'Calendar' ), 'Calendar'[Year#] = CurrentYear - 1 ),

    "QtyBeforePY", CALCULATE( SUM ( Ord_Tbl[Units] ), REMOVEFILTERS( 'Calendar' ), 'Calendar'[Year#] < CurrentYear - 1 )

),

KEEPFILTERS(Ord_Tbl[Product] IN {"Product A", "Product B"})

)

RETURN

COUNTROWS(

    FILTER (

        CustomersAndFirstPurchaseCY,

        NOT ISBLANK([FirstPurchaseDateCY]) && [QtyCY]>0 && [QtyPY] = 0 && [QtyBeforePY] >0 && [FirstPurchaseDateCY]<=MaxVisibleDate && [FirstPurchaseDateCY] >= MinVisibleDate

    )

)

Please check that it is correct, I do not see anything before 2025, plausible?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[etane]

Thank you for this.  It works perfectly in my test file.  However, I am not successful at adapting it to my actual working file.  Maybe the test data I provided is not accurately reflecting the actual data enough.  If I provide a more robust data set, could you please review it and let me know what adjustment has to be made to your original submission?