Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
I need to count the active clients of each month. A client is considered active when he trades. There are two dates. Open trade date and Close trade date. The main problem is between those dates.
For example a client opened a trade on 01/09/2019 and close it on 31/12/2018. It is easy for me to say that the client was active in September and in December. How can I count him as active in October and November also?
Thank you in advance.
Solved! Go to Solution.
In continuation of the above, the below is the solution I found.
I prepair an excel file and I need ideas for translation to dax, as the mentioned solutions didn't help me.
Below are my data.
I create the below table by adding Months and I used formula with result 1 my client was active and 0 if wasn't during the month.
Formula: =IF(AND((EOMONTH(D$1,0)>=$B2),(D$1<EOMONTH($C2,0))),1,0)
Then I summarize the table per client.
=IF(SUMIFS(Data!D$1:D$999,Data!$A$1:$A$999,Results!$A2)=0,0,1)
In continuation of the above, the below is the solution I found.
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
the dates should be disconnected from your Calendar table (no joins)
this measure will work then if you put both the customer, year and month in the visual as well
Customer Status = VAR __CurrentDate = MAX(Calendar[Date]) VAR __CustOpen = MIN('Table'[Open trade date]) VAR __CustClose = MAX('Table'[Close trade date]) RETURN IF( __CurrentDate >= __CustOpen && __CurrentDate <= __CustClose, "Active", BLANK())
Measure = VAR __CurrentMonth = MAX('Calendar'[Date]) VAR __ActiveCustomers = FILTER('Table',__CurrentMonth >= 'Table'[Open trade date] && __CurrentMonth<= 'Table'[Close trade date]) RETURN COUNTROWS(__ActiveCustomers)
Hello Sir,
I forgot to say that 1 client has multiple trades. I can't take min and max because a client may open a trade on 01/01/2018 close on 01/03/2018 and then open another trade on 01/06/2018 and close it on 01/10/2018. With the above method the client will be consider as active from January to October, while actully he was inactive in April and May.
See the below example:
hmm, but do you actually need a flag that says "Active"? In the original post you only ask for the count of active customers
with monthly granularity this measure will work as is, also considering the multiple trades complexity
NrOfActiveClients = VAR __CurrentMonth = MAX('Calendar'[Date]) VAR __ActiveCustomers = FILTER('Table',__CurrentMonth >= 'Table'[Open trade date] && __CurrentMonth<= 'Table'[Close trade date]) RETURN COUNTROWS(__ActiveCustomers)
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
96 | |
89 | |
73 | |
61 | |
58 |