Average day of repurchase by product and each customer

[Anonymous]

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.

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

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

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

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.

[LandonForet]

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 · Accepted solution]

Hi,

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

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

[Anonymous]

The result is perffect. Thank you for your time.