Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity 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
Icon for Community Support rankCommunity 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
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity Champion
    6 years ago

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