Forum Discussion

Marcus_S's avatar
Marcus_S
Frequent Visitor
4 years ago
Solved

Calculate intervall off orders

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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)

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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] )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.