Forum Discussion

snandy2011's avatar
snandy2011
Helper IV
7 years ago
Solved

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...
Need Help
Tips and Tricks
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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.

    How-to-count-user-within-specific-time-frame-u

     

    Best Regards,
    Dale

snandy2011's avatar
snandy2011
Helper IV
7 years ago

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's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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.

How-to-count-user-within-specific-time-frame-u

 

Best Regards,
Dale

  • snandy2011's avatar
    snandy2011
    Helper IV
    7 years ago

    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