Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
littlemojopuppy
Community Champion
Community Champion

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 that date.  Meaning for customers who logged in on May 31, how many of those customers logged in each of the periods June 1 - 7, June 8 - 14, June 15 - 21, etc.

I came up with a long formula that is correct.

Users Returning Within 42 Days:=VAR Weeks = 5
RETURN
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( IdentityLog[IdentityId] ),
            -- Current Week
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    DATEADD (
                        LASTDATE ( 'Calendar'[Date] ),
                        ( Weeks * 7 ) + 1,
                        DAY
                    ),
                    7,
                    DAY
                )
            ),
            -- Previous Week
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    DATEADD (
                        LASTDATE ( 'Calendar'[Date] ),
                        ( ( Weeks - 1 ) * 7 ) + 1,
                        DAY
                    ),
                    7,
                    DAY
                )
            ),
            -- Two Weeks Ago
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    DATEADD (
                        LASTDATE ( 'Calendar'[Date] ),
                        ( ( Weeks - 2 ) * 7 ) + 1,
                        DAY
                    ),
                    7,
                    DAY
                )
            ),
            -- Three Weeks Ago
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    DATEADD (
                        LASTDATE ( 'Calendar'[Date] ),
                        ( ( Weeks - 3 ) * 7 ) + 1,
                        DAY
                    ),
                    7,
                    DAY
                )
            ),
            -- Four Weeks Ago
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    DATEADD (
                        LASTDATE ( 'Calendar'[Date] ),
                        ( ( Weeks - 4 ) * 7 ) + 1,
                        DAY
                    ),
                    7,
                    DAY
                )
            ),
            -- Five Weeks Ago
            CALCULATETABLE (
                VALUES ( IdentityLog[IdentityId] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    DATEADD (
                        LASTDATE ( 'Calendar'[Date] ),
                        ( ( Weeks - 5 ) * 7 ) + 1,
                        DAY
                    ),
                    7,
                    DAY
                )
            )
        )
    )

I was curious if anyone could suggest a more elegant solution.  I did see @Greg_Deckler's blog post on For and While Loops in DAX.  But I couldn't get it to work properly because I need to pass groups of User IDs to compare and not a single value.

 

Any help would be appreciated!

6 REPLIES 6
v-lionel-msft
Community Support
Community Support

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.

 

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.

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.

aaa10.PNG

 

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.

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.

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

I'll try it out...thank you!  🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors