To calculate average days between purchases and days since last purchase

[Kella]

I'm new to power BI.Basically I need to find for #1) Average days between purchases and  #2) Days since last purchase. From these 2 measure I can display days between most recent purchases for #1 and average days between purchase for #2  for selected grouping.I hope I have explained my question in a proper way in the below table.

SalesDate	InvoiceNo	Customer Account	InvoiceAmt
7/20/2023	50465123	2881122	100
12/28/2022	43435546	5553332	400
9/17/2023	33332211	7744333	488.8
1/1/2019	76555544	1021223	90.2
10/26/2022	33322221	2881122	5
11/17/2023	33332231	7744333	42.2
3/1/2019	76555542	1021223	30
4/20/2023	90465123	2881122	120

So,I need a dax query which can calculate for 2 items above.Thank you for your help!

[MasterSonic]

Try 

Average Days Between Purchases = 

    AVERAGE(

        DATEDIFF(

            MIN(SalesDate),

            CALCULATE(

                MIN(SalesDate),

                FILTER(

                    ALL(SalesDate),

                    SalesDate > MIN(SalesDate)

                )

            ),

            DAY

        )

    )

And

Days Since Last Purchase = 

    DATEDIFF(

        MAX(SalesDate),

        TODAY(),

        DAY

    )

[Kella]

I had tried for Average Days Between Purchases but it not works.However for Days Since Last Purchase is works fine.Thanks

[Anonymous]

Hi @Kella 

You can refer to the following ways to calculate average

create two measures

Measure = DATEDIFF(MIN([SalesDate]),CALCULATE(MIN([SalesDate]),FILTER(ALLSELECTED('Table'),'Table'[SalesDate]>MIN([SalesDate])&&[       Customer Account]=MAX([       Customer Account]))),DAY)

Measure 2 = DIVIDE(SUMX(FILTER(ALLSELECTED('Table'),[       Customer Account]=MAX([       Customer Account])),[Measure]),COUNTROWS(FILTER(ALLSELECTED('Table'),[       Customer Account]=MAX([       Customer Account])&&[Measure]<>BLANK())))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @Kella 

for averge, try like:

Average Days Between Purchases =
DIVID(
    MAX(TableName[SalesDate]) - MINX(TableName[SalesDate]),
    COUNTROWS(VALUES(TableName[SalesDate]))-1
)

[Kella]

Thanks @FreemanZ .I had tried but not works and return with blank.