How to count user within specific time frame

[snandy2011]

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

[dedelman_clng]

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?

[snandy2011]

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

[v-jiascu-msft · Accepted solution]

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

[snandy2011]

Hi @v-jiascu-msft,

Thank you very much for your solution..It worked perfectly,when i used to my data. Although, i achieved it in excel..But in  Power Bi, a new thing, i have learned..

Thanks for your effort and solution.

Sincerely,

snandy