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 custo...
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.

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