Forum Discussion
How to count user within specific time frame
Hi all,
I am going to work on a situation where i do need a help. Below is the scenario,
Suppose i have some user deposited data (user who have deposited amount). Lets say,
User Amount Date
A 100 10/4/2018
B 300 25/03/2018
D 500 5/8/2018
A 200 29/04/2018
E 300 14/7/2018
F 600 18/7/2018
B 500 20/03/2018
C 400 9/9/2018
D 800 30/08/2018
E 10 20/08/2018
A 650 5/5/2018
A 500 20/8/2018
Now, the twist is if the user deposits the amount again within 30 days from his first deposit date, then i will treat him as a First Time Depositor (FTD) else other.
For example, user A's first deposit date is 10/4/2018, within 30 days he has deposited 2 times more (29/04/2018 and 5/5/2018), So he is my FTD. In the case of B's first deposit date is 20/03/2018, within 30 days he has deposited 1 times (25/03/2018). So he is also my FTD.. But, E's first deposit date is 14/7/2018, but within 30 days he has not deposited..so, E is my "other user".
Now, if i apply the above logic then my first time depositor are
User Amount Date User type
A 100 10/4/2018 FTD
B 300 25/03/2018 FTD
D 500 5/8/2018 FTD
A 200 29/04/2018 FTD
E 300 14/7/2018 Other
F 600 18/7/2018 Other
B 500 20/03/2018 FTD
C 400 9/9/2018 Other
D 800 30/08/2018 FTD
E 10 20/08/2018 Other
A 650 5/5/2018 FTD
A 500 20/8/2018 FTD
So my expected result will be
Total FTD user Total FTD Amount Total Other User Total Other user Amount
3 3550 3 1310
Can you please help me how to achieve this logic by writing DAX or power query?
Please share some ideas and suggesations.
Any suggesations is really appreciable.
Thanks
snandy2011
Hi snandy2011,
Please download the solution from the attachment.
1. Create a column.
Column = VAR firstDeposit = CALCULATE ( MIN ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[User] ) ) VAR records30Days = CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALLEXCEPT ( Table1, Table1[User] ), 'Table1'[Date] >= firstDeposit && 'Table1'[Date] <= EDATE ( firstDeposit, 1 ) ) ) RETURN IF ( records30Days > 1, "FTD", "Other" )
2. Create four measures.
Best Regards,
Dale
4 Replies
Hi snandy2011 - do all deposits have to be within 30 days to be considered a FTD, or as long as one or more deposits is within the initial 30 day window it is a FTD?
Hi dedelman_clng, Thanks for reply. I have just edited something.Hope, you will be cleared.
Each deposit user's deadline is different. For example, A's first deposit date is 10/4/2018. so therefore A's deadline is 10/05/2018 (10/4/2018+30 days=10/05/2018). but B's deadline is 20/04/2018 ( B's first deposit date is 20/03/2018, so after 30 days, his deadline will be 20/04/2018).Within their deadline, if any user deposits, then i will considered as FTD, otherwise Other..
One thing please you notice, A is my FTD and his deadline is 10/05/2018. But, after 10/05/2018, he again deposited 500 amount at 20/08/2018.Since he deposied twice within his deadline and he is my FTD, so i will add his 500 amount for total FTD amount.
I hope you will be cleared. Any idea, how to achieve that..
If anything else you need to know, please let me know.
Thanks,
snandy2011
Hi snandy2011,
Please download the solution from the attachment.
1. Create a column.
Column = VAR firstDeposit = CALCULATE ( MIN ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[User] ) ) VAR records30Days = CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALLEXCEPT ( Table1, Table1[User] ), 'Table1'[Date] >= firstDeposit && 'Table1'[Date] <= EDATE ( firstDeposit, 1 ) ) ) RETURN IF ( records30Days > 1, "FTD", "Other" )
2. Create four measures.
Best Regards,
Dale
