Reporting: New Customer (that paid), New Consignment (new customer not paid)

Dear friends,

I am trying to build a report that shows in this month or trend over time:
Number of New Retailer,
Number of New Consignment,
Number of Unsuccessful Consignment,
Number of Successful Consignment also counted as New Retailer. But I need to know Number of Successful Consignment to calculate converting rate over time.

Details of definition:

  • How many NEW RETAILER? My definition of NEW RETAILER is first time order, and paid on the same order date (or paid within 7 days after order date)
  • How many NEW CONSIGNMENT? My definition of NEW CONSIGNMENT is first time order, and not paid on the same order date (or paid later than 7 days after order date) (or the paid date column is empty also consider as new consignment)
  • Over time, if the NEW CONSIGNMENT returns the goods (means they can not sell, and do not want to be my retailer), then consider as NOT SUCCESSFUL CONSIGNMENT.
  • Over time, if NEW CONSIGNMENT paid money (after more than 7 days), and order 2nd time, the report as SUCCESSFUL CONSIGNMENT and become NEW RETAILER.

Attachment is the sample file.

Best Regards,

Kvuong

DIM_Order.xlsx (64.1 KB) Sales_Details.xlsx (178.8 KB)

Hi @kvuong2010. Thanks for the data. Could you please post your work-in-progress PBIX file and a screenshot or Excel mock-up of your desired outcome to help the forum members visualize and analyze your issue? Also, if you are posting any DAX code, please use the built-in formatter.
Greg
_eDNA Forum - Format DAX

Thanks for posting your question @kvuong2010 To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above and the advice from Greg, will likely enable a quick solution to your question.

Hi Greg,

I prepare the “Data model”, “Raw data”, and sample of “Report that I wish to see”. I also update my definition as in the Excel file.

PS: I am new to this forum so please kindly guide me how to provide the data that you could support me best.
And that Data model is basically blank as I do not know how to write DAX to provide the report I want.

Report that wish to see.xlsx (9.9 KB)
DNA Support.xlsx (121.7 KB) Sales_DNA Support.pbix (89.5 KB)

Dear Greg and friends,

I hope someone takes time to help me on this request.

Much appreciated,

Kvuong

Hello @kvuong2010, due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. When starting a new post, I suggest linking this thread, provide as much context to a question as possible and include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. Thanks!

Dear EnterpriseDNA,

Is it a joke to say the post be solved? Unbelievable!

@kvuong2010 What have you tried in the past 13 days?

@AntrikshSharma, Hi,

I try to follow the instruction in “How Many New Customers Do You Have Each Month - Advanced Power BI Analytics”

----- DAX Start------
New Customers = //calculating which customers within any particular month have purchased but haven’t done so for the last x number of days (ChurnDate)
VAR CustomerTM = VALUES( ‘SalesOrder’[Customer] )
VAR PriorCustomers = CALCULATETABLE( VALUES( ‘SalesOrder’[Customer] ),
FILTER( ALL( ‘0_DIM_Dates’ ),
‘DIM_Dates’[Date] > MIN( ‘DIM_Dates’[Date] ) - [Churn Time Frame Value] &&
‘DIM_Dates’[Date] < MIN( ‘DIM_Dates’[Date] ) ) )

RETURN
COUNTROWS(
EXCEPT( CustomerTM, PriorCustomers ) )

----- DAX End------

But the result is wrong, I put [Churn Time Frame Value] = 450
and the first month from the DAX shows 15 new customers, but I counted 38 customer in that month (as of course there were 38 new customers).

Then I tried to set up the following for “New Consignment Customers” and “New Paid Customers” but all wrong!
as they dont add up!

----- DAX Start------
New Paid Customers = CALCULATE( [New Customers] ,
FILTER( ‘SalesOrder’ ,
‘SalesOrder’[PaidDate] <> BLANK () ) )

----- DAX End------

----- DAX Start------
New Consignment Customers = CALCULATE( [New Customers] ,
FILTER( ‘SalesOrder’ ,
‘SalesOrder’[PaidDate] = BLANK () ) )

----- DAX End------

As in the screenshot below, 2019/12: “New customers” is 18, but “New Paid Customers” is 25 and "“New Consignment Customers” is 2!

Hope there is solution!

Much appreciated!
KV