Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
6 years ago

Customer Retention Calculation Over Successive Weeks

Hi!  Looking for a little advice.  Requirement is to calculate the number of customers who logged in on a given date, and then who logged in again in each of of the six one week time periods from tha...
Need Help
v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

Hi littlemojopuppy ,

 

Maybe you can add an [Index] and simplify the code.

Users Returning Within 42 Days = 
VAR Weeks = 5
RETURN
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( IdentityLog[IdentityId] ),
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    DATEADD (
                        LASTDATE ( 'Calendar'[Date] ),
                        ( (Weeks- MAX([Index] )* 7 ) + 1,
                        DAY
                    ),
                    7,
                    DAY
                )
            )
    )
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    6 years ago

    I'll try it out...thank you!  ğŸ™‚

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    6 years ago

    Tried it out...did not work.  The problem is that using MAX(Index) only looks at the week/index value furthest in time.  I need to look at each individual 7 day period starting with today up to the week/index.  Really could use looping but that doesn't seem like its' possible.

    • v-lionel-msft's avatar
      v-lionel-msft
      Community Support
      6 years ago

      Hi littlemojopuppy ,

       

      Or like this?

      Users Returning Within 42 Days = 
VAR Weeks = 5
RETURN
COUNTROWS (
        CALCULATETABLE (
            VALUES ( IdentityLog[IdentityId] ),
            
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                // 0
                DATESINPERIOD ( 
                    'Calendar'[Date],
                    DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( (Weeks-0 )* 7 ) + 1, DAY ),
                    7, DAY
                ),
                // 1
                DATESINPERIOD ( 
                    'Calendar'[Date],
                    DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( (Weeks-1 )* 7 ) + 1, DAY ),
                    7, DAY
                ),
                // 2
                DATESINPERIOD ( 
                    'Calendar'[Date],
                    DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( (Weeks-2 )* 7 ) + 1, DAY ),
                    7, DAY
                ),
                //3
                DATESINPERIOD ( 
                    'Calendar'[Date],
                    DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( (Weeks-3 )* 7 ) + 1, DAY ),
                    7, DAY
                ),
                // 4
                DATESINPERIOD ( 
                    'Calendar'[Date],
                    DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( (Weeks-4 )* 7 ) + 1, DAY ),
                    7, DAY
                ),
                 // 5
                DATESINPERIOD ( 
                    'Calendar'[Date],
                    DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( (Weeks-5 )* 7 ) + 1, DAY ),
                    7, DAY
                ),
                //5
                 DATESINPERIOD ( 
                    'Calendar'[Date],
                    DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( (Weeks-5 )* 7 ) + 1, DAY ),
                    7, DAY
                )
            )
        )
)

      If you need to use the CALCULATETABLE() function, it seems that you can only list each filter, you can not list it in a loop.

       

      Best regards,
      Lionel Chen

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        6 years ago

        Good morning!

         

        Using CALCULATETABLE isn't mandated.  Need to find those users that logged in in each of the six weeks and I can certainly try this out.  Was hoping that I could avoid having to list out each successive week but I may not be able to.

        I found this article by marcorusso on implementing compound interest.  Compound interest calculations have to build on successive periods so I thought I would try it, but not hopeful that it would work because the formula would return values for periods and not groups of users/IdentityIDs from each period.  That's really where it gets stuck...

        Thanks for looking at it again.