Calculate intervall off orders

[Marcus_S]

Hello everyone!

A colleague would like to have an overview that shows the rythm in which the customer always orders in order to anticipate the next order.
We have a table that contains the customer number, the order number and the order date.
The problem for me is how Power BI can calculate the datediff of the orderdate between row 1 and 2 then between row 2 and 3 and so on. And then calculate an average from the calculated datediffs.

Thank you very much

[Anonymous · Accepted solution]

Hi @Marcus_S ,

My table is :

You can follow the below steps:

1.Add a new column in the table to calculate the datediff

Date_Diff =
VAR curdate = 'Table'[Order date]
VAR prevoius_date =
    CALCULATE (
        MAX ( 'Table'[Order date] ),
        FILTER ( 'Table', 'Table'[Order date] < curdate )
    )
RETURN
    DATEDIFF ( prevoius_date, curdate, DAY )

2.Create a measure to calculate the average about the new added column

Measure =
AVERAGE ( 'Table'[Date_Diff] )

If I misunderstand your demands, please feel free to let me know.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your data model.

It is for creating a measure.

Rythm average measure: =
VAR _currentcustomerid =
    MAX ( Customer[Customer ID] )
VAR _newtable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                FILTER ( ALL ( Data ), Data[Customer ID] = _currentcustomerid ),
                "@previousorderdate",
                    MAXX (
                        FILTER (
                            Data,
                            Data[Customer ID] = EARLIER ( Data[Customer ID] )
                                && Data[Order Date] < EARLIER ( Data[Order Date] )
                        ),
                        Data[Order Date]
                    )
            ),
            [@previousorderdate] <> BLANK ()
        ),
        "@datediff", INT ( Data[Order Date] - [@previousorderdate] )
    )
RETURN
    IF (
        HASONEVALUE ( Customer[Customer ID] ),
        AVERAGEX ( _newtable, [@datediff] )
    )

[amitchandak]

@Marcus_S , Try a new column like

New column = datediff( maxx(filter(Table, [Customer number] = earlier([Customer number]) && [Order Date] = earlier([Order date])),[Order date]),[Order Date] ,day)