Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to calculate New Clients and Existing Clients every week. My data model is as shown in the figure:
billing_entries:
Has all Billing related information for Clients by dateofservice(dd/mm/yyyy)
The dates(dt) table is connected to billing(dateofservice), where dates[dt] has dates from 1/1/1900 -1/1/2099
Clients table has ~(70) unique Client ID's; Billing(~90 unique clientID's) now, I'm trying to find out the information only about the clients in the Client table.
i.e. # of new clients that are visiting every week
new client: If the client_id doesn't exist in Billing Table for 365 days consecutively, It is considered new next time it shows up or if it's new entirely.
Returning Client: On a particular day, If the client_id is seen atleast once in last 365 days, then it is considered as returned client.
I came up with bunch of DAX calculations, but none of them seemed to work.
New Clients = COUNTROWS( FILTER( ADDCOLUMNS(VALUES(billing_entries[clientid]), "Previous Billing", CALCULATE(COUNTROWS(billing_entries), FILTER( ALL(dates), dates[dt]<MIN(dates[dt]) ) ) ), [Previous Billing]=0 ) )
new_client v1 = COUNTROWS( FILTER(VALUES(billing_entries[clientid]), CALCULATE( COUNTROWS(billing_entries), FILTER(DISTINCT(ALL(billing_entries)), billing_entries[dateofservice]<MAX(billing_entries[dateofservice])))))
new_client_v2 = VAR First_time_attending = ADDCOLUMNS( ALL(billing_entries[clientid]),"First Attendance Date", CALCULATE(MIN(billing_entries[dateofservice]),ALLEXCEPT(billing_entries,billing_entries[clientid]))) VAR NewClient = FILTER( First_time_attending, CONTAINS(VALUES(dates[dt]),dates[dt],[First Attendance Date])) RETURN CALCULATE(SUM(billing_entries[chargeagreed]),NewClient)
I hope this gives some idea about the problem. I appreciate any help in solving this problem.
Thanks,
Krish
Hi @krish003,
I'm not so sure for your formula, maybe you can try to use following formula to calculate client id if it works on your side:
new client = VAR temp = SELECTCOLUMNS ( billing_entries, "clientid", [clientid], "current date", billing_entries[Date] ) VAR list = ADDCOLUMNS ( temp, "Previous", MAXX ( FILTER ( temp, [clientid] = EARLIER ( [clientid] ) && [current date] < EARLIER ( [current date] ) ), [current date] ) ) RETURN COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( list, [previous] = BLANK () || DATEDIFF ( [Previous], [current date], DAY ) >= 365 ), "ID", [clientid] ) ) )
If above not help, can you please share some sample data to test and coding formula?
Regards,
Xiaoxin Sheng
Hi Sheng,
The DAX doesn't work; I was able to figure our a temp work around for the problem by creating a new dummy table and extract the required data. Let me send you some sample data so that you'll have better idea.
Thanks
Krish
HI @krish003,
You can upload to onedrive or google drive, then share link here.
Notice: I not need real data, if you table contains sensitive data, please create some test data with similar table structure and relationship for test.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |