Forum Discussion

snandy2011's avatar
snandy2011
Icon for Helper IV rankHelper 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

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
    snandy2011
    Icon for Helper IV rankHelper 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
      Icon for Microsoft Employee rankMicrosoft 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
        Icon for Helper IV rankHelper 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