skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Partners Overview
    • Solutions Partners
    • BI Specialized Partners
    • Power BI CSOs
    • Fabric Partner Community
    • Training
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Power BI forums
    • Updates
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish forums
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 

    Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

    • Power BI forums
    • Galleries
    • Quick Measures Gallery
    • Customer Retention Part 3: Period Of Stay – Cohort...

    Customer Retention Part 3: Period Of Stay – Cohort Analysis

    09-24-2020 07:22 AM - last edited 04-05-2021 09:20 AM

    Super User amitchandak
    Super User
    5168 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    amitchandak
    Super User amitchandak
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Customer Retention Part 3: Period Of Stay – Cohort Analysis

    ‎09-24-2020 07:22 AM

    Objective:  

    Period Of Stay – Cohort Analysis provide visibility on how many customers were retained after their first date of purchase.

    Cohort Analysis is studying the behavioral analysis of customers.

     

    Assume there are 100 new customers (consumers who made the first purchase in the store) in Jan 2020. Out of these 100, how many customers came back in the second month (Feb 2020). Then how many returned in the third month (March -2020) and so on for every month in 2020.

     

    Columns

     

    First Sales = minx(FILTER(Sales,[Customer Id] =EARLIER([Customer Id])),[Sales Date])
    Customer Age = DATEDIFF([First Sales],[Sales Date],MONTH)+1

     

     

    Table 

     

    Customer Age Bucket = ADDCOLUMNS(GENERATESERIES(1,max(Sales[Customer Age])+1) ,"Age in Month" , "Month " &[Value])
    

     

     

    Measures

     

    Customers = DISTINCTCOUNT(Sales[Customer Id])
    
    Retain % = CALCULATE(divide(DISTINCTCOUNT(Sales[Customer Id]),CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =1)) , 'Customer Age Bucket'[Age] >1)
    

     

     

     

     

    eyJrIjoiYWM4MGY3ZTUtZmZhZS00ZDQ4LWE1NzUtMGUwMDc3N2U4MmI0IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9



    !! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
    Microsoft Power BI Learning Resources, 2023 !!
    Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
    Customer Cohort.pbix
    Labels:
    • Labels:
    • Featured
    • Financial
    • Time Intelligence
    Message 1 of 6
    5,168 Views
    6
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    ewu
    ewu Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-28-2023 07:12 PM

    Thanks. I am stuck at the Measures steps - 3rd step in the response above. How do you keep the age bucket in the sales table?

    Message 5 of 6
    542 Views
    0
    Reply
    amitchandak
    Super User amitchandak
    Super User
    In response to ewu
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-28-2023 07:17 PM

    @ewu ,There is column rename value -> age (is that creating confusion)

     

     

    In that case you create a new column in sales

     


    Customer Age Bucket = "Month " & format([Customer Age],"00") 

     

    You can also use measures like

     

    Retain % = divide(DISTINCTCOUNT(Sales[Customer Id]),CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),[Customer Age] =1))



    !! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
    Microsoft Power BI Learning Resources, 2023 !!
    Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
    Message 6 of 6
    541 Views
    0
    Reply
    ewu
    ewu Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-28-2023 06:20 PM

    After the First Sales and Customer Age Columns are done, and then Customer Age Bucket Tables is created, then Measures steps for Retain %, I guess, are created in the original data table? How is it linked to the Customer Age Bucket table?  @amitchandak @Annie_Do_1413 

    Message 3 of 6
    549 Views
    0
    Reply
    amitchandak
    Super User amitchandak
    Super User
    In response to ewu
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-28-2023 06:55 PM

    @ewu , Customer Age has been calculated using the first sales date and sales date. That has been joined with the dimension table. If needed we can keep the Age bucket in the sales table.

    Power BI Cohort Analysis, Customer Retention %- https://youtu.be/qY1SDF1cwsg



    !! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
    Microsoft Power BI Learning Resources, 2023 !!
    Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
    Message 4 of 6
    544 Views
    0
    Reply
    Annie_Do_1413
    Annie_Do_1413
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-26-2022 07:43 PM

    Great! Thanks for your sharing

    Message 2 of 6
    1,140 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices