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
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.

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      6 years ago

      So the code I originally posted was the only thing that worked successfully.  But thank you for your input!  ğŸ˜Š