最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

powerbi - Power BI DAX - Determine the Percentage of returning customers within 90 days of First Purchase and within the followi

programmeradmin2浏览0评论

I am trying to create DAX expressions to determine the percentage of customers that returned 90 days after their first purchase and also determine the percentage of customers that returned 3 months after the first 90 days.

There is a Customers Table which has the following columns: CustomerKey, DateFirstPurchase and there is a Sales Table which has the following columns: CustomerKey, OrderDate.

Below is a very sample of the data in each table and the end result expected from the full data to be a reference for the ask. I have been able to determine the first 2 columns of the expected results, but need help on the percentages in the 3rd and 4th columns. Thanks!

CUSTOMERS TABLE

CustomerKey DateFirstPurchase
25640   8/1/03 0:00
37688   8/1/03 0:00
26192   8/1/03 0:00
33962   8/1/03 0:00
24329   8/1/03 0:00
34293   8/1/03 0:00
33556   8/1/03 0:00
39079   8/1/03 0:00
32330   8/1/03 0:00
25507   8/1/03 0:00
24567   8/1/03 0:00
21746   8/1/03 0:00
30200   8/1/03 0:00
34940   8/1/03 0:00
38788   8/1/03 0:00
37659   8/1/03 0:00
26338   8/1/03 0:00
31407   8/1/03 0:00
32284   8/1/03 0:00
22204   8/1/03 0:00
25856   8/1/03 0:00
33941   8/1/03 0:00
29344   8/1/03 0:00
26982   8/1/03 0:00
25703   8/1/03 0:00

SALES Table

CustomerKey OrderDate
25640   8/21/03 0:00
25640   8/21/03 0:00
25640   8/21/03 0:00
26192   4/24/04 0:00
26192   10/28/02 0:00
24329   6/22/04 0:00
24329   6/22/04 0:00
25507   10/7/03 0:00
24567   9/21/03 0:00
24567   9/21/03 0:00
21746   5/24/04 0:00
21746   5/24/04 0:00
26338   10/13/02 0:00
26338   2/29/04 0:00
26338   2/29/04 0:00

RESULT FOR FULL DATA AS A REFERENCE

Edit:

I've created a Calendar Table using the following expression:

Calendar = CALENDAR(FIRSTDATE(Sales[OrderDate]), LASTDATE( Sales[OrderDate]))

I've also tried the following for the % of customers who returned about 90 days and did not get the correct result, but I feel like it's on the right track:

Purchase within 90days(%) = 
VAR Customerlist = VALUES( Sales[CustomerKey] )
VAR Returndate = 90
VAR CountCustomers =  
COUNTROWS( 
    FILTER(
        Customerlist, 
        CALCULATE( COUNTROWS (Sales), 
            FILTER( ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] > ( MIN ( Sales[OrderDate] ) ) &&
             'Calendar'[Date] <  MIN ( Sales[OrderDate]) + Returndate 
             )
        ) >0
    )
)
Return
DIVIDE(CountCustomers,[Customers])

与本文相关的文章

发布评论

评论列表(0)

  1. 暂无评论