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
bbbb
Frequent Visitor

Average day of repurchase by product and each customer

Hi, 

I would like to average day that customer repurchase by each product and average day that each customer buy again . sorry for inconvenience becasue I got the error, when i post the data with table.

 

bbbb_0-1699201453002.png

 

1. For example, average day that customer repurchase by each product

From the data, Average Day repurchase by product become as table below

bbbb_1-1699201501476.png

First product, customer A bought on 29 Oct and 3 Nov so result of average day repurchase should be 5

 

Second product, costumer B bought on 29 Oct and 3 Nov(Day diff. = 5), customer c bought on 29 Oct and 2 Nov(Day diff. = 2), customer B also bought on 5 Nov (Day diff= 2 from the last day(3 Nov ) by the same product and the same customer purchase ).The result of average day repurchase should be(5+2+2)/3=3 day

 

However, costumer C bought packages 3 and costumer A bought packages 2 on 4 Nov.This is first time of buying so not should be counted.

 

2. For example, average day that customer repurchase by each customer

As the same data, average day repurchase by each customer become as table below

bbbb_2-1699201562838.png

For customerA, he brought on 29 Oct,3 Nov and 4 Nov. It has 2 period. Date diif are 5 and 1 so result of average day repurchase by each customer should be 3 days.

 

For customerB, he bought on 29 Oct, 3 Nov( 2 times ), 4 Nov, 5 Nov and 12 Nov. It has 5 period. Date diif are 5 ,0,1,1 and 7  so result of average day repurchase by each customer should be 5.8 days.

 

For customerC, he bought on 29 Oct, 2 Nov and 4 Nov. It has 2 period. Date diif are 4  and 2  so result of average day repurchase by each customer should be 3 days.

 

Pls, Help me to figure out how can I do this in measure? If you also have any suggestion, let's me know.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1699251116069.png

 

 

Average day repurchase by package: = 
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, Data[Customer Name], Data[Date] ),
            "@currentrowdate", CALCULATE ( MAX ( Data[Date] ) ),
            "@prevrowdate",
                MAXX (
                    FILTER (
                        Data,
                        Data[Date] < EARLIER ( Data[Date] )
                            && Data[Customer Name] = EARLIER ( Data[Customer Name] )
                    ),
                    Data[Date]
                )
        ),
        [@prevrowdate] <> BLANK ()
    )
VAR _diff =
    ADDCOLUMNS ( _t, "@diff", [@currentrowdate] - [@prevrowdate] )
RETURN
    DIVIDE ( SUMX ( _diff, [@diff] ), SUMX ( _diff, 1 ) )

 

Average day repurchase by customer: =
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, Data[Packages Purchase], Data[Date] ),
            "@currentrowdate", CALCULATE ( MAX ( Data[Date] ) ),
            "@prevrowdate", MAXX ( FILTER ( Data, Data[Date] < EARLIER ( Data[Date] ) ), Data[Date] )
        ),
        [@prevrowdate] <> BLANK ()
    )
VAR _diff =
    ADDCOLUMNS (
        SUMMARIZE ( _t, [@currentrowdate], [@prevrowdate] ),
        "@diff", [@currentrowdate] - [@prevrowdate]
    )
RETURN
    DIVIDE ( SUMX ( _diff, [@diff] ), SUMX ( _t, 1 ) )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
LandonForet
New Member

Thanks for answering, I appreciate you. That helps a lot.

Spoiler
To calculate the average days between repurchases in Power BI, you can use DAX measures. For average days repurchase by each product, the measure considers distinct products and calculates the average repurchase interval. Players of games of skill or chance often attribute a significant portion of their fate to luck. Some ask How to get lucky at the casino - Attract Gambling Luck and to know How to get lucky at the casino This belief is especially prevalent in casinos where enthusiasts engage in customs and use lucky charms to sway the favor of gaming deities. However, the odds of winning or losing are determined by machines and mathematical probabilities, and these customs do not directly influence the outcomes. Despite this, these rituals can have a psychological impact on the gaming experience. It is important to maintain a balance between embracing these traditions for enjoyment and avoiding transforming them into undue obsessions.
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1699251116069.png

 

 

Average day repurchase by package: = 
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, Data[Customer Name], Data[Date] ),
            "@currentrowdate", CALCULATE ( MAX ( Data[Date] ) ),
            "@prevrowdate",
                MAXX (
                    FILTER (
                        Data,
                        Data[Date] < EARLIER ( Data[Date] )
                            && Data[Customer Name] = EARLIER ( Data[Customer Name] )
                    ),
                    Data[Date]
                )
        ),
        [@prevrowdate] <> BLANK ()
    )
VAR _diff =
    ADDCOLUMNS ( _t, "@diff", [@currentrowdate] - [@prevrowdate] )
RETURN
    DIVIDE ( SUMX ( _diff, [@diff] ), SUMX ( _diff, 1 ) )

 

Average day repurchase by customer: =
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, Data[Packages Purchase], Data[Date] ),
            "@currentrowdate", CALCULATE ( MAX ( Data[Date] ) ),
            "@prevrowdate", MAXX ( FILTER ( Data, Data[Date] < EARLIER ( Data[Date] ) ), Data[Date] )
        ),
        [@prevrowdate] <> BLANK ()
    )
VAR _diff =
    ADDCOLUMNS (
        SUMMARIZE ( _t, [@currentrowdate], [@prevrowdate] ),
        "@diff", [@currentrowdate] - [@prevrowdate]
    )
RETURN
    DIVIDE ( SUMX ( _diff, [@diff] ), SUMX ( _t, 1 ) )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


The result is perffect. Thank you for your time.

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