Forum Discussion

Adhavan's avatar
Adhavan
Frequent Visitor
3 years ago
Solved

Calculate Average Order frequency

Hi All, I have dataset like below, I want to show =Average order frequency.   Like, we are reciving the order for every X minutes.    I have tried different calculation as suggested by in the fo...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Adhavan ,

     

    Here I suggest you to try this code to create a calculated column.

    Diff column =
VAR _LASTORDER =
    CALCULATE ( MIN ( 'Table'[order id] ), ALLEXCEPT ( 'Table', 'Table'[date] ) )
VAR _DIFF =
    [Datetime]
        - MAXX (
            FILTER ( 'Table', 'Table'[order id] = EARLIER ( 'Table'[order id] ) - 1 ),
            [Datetime]
        )
RETURN
    IF ( 'Table'[order id] = _LASTORDER, BLANK (), _DIFF )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

  • mangaus1111's avatar
    mangaus1111
    3 years ago

    Hi Adhavan ,

    it is a best practice to avoid using EARLIER to make the code easier to author and maintain, using variable (VAR), like in this example:
     
    Diff column =
    VAR FirstOrder_of_the_Day =
        CALCULATE (
                 MIN ( 'Table'[Order ID] ),
                 ALLEXCEPT ( 'Table', 'Table'[Date] )
                 )

    VAR Order_ID = 'Table'[Order ID]

    VAR Diff =
        [DateTime]
            - MAXX (
                    FILTER ('Table',
                            'Table'[Order ID] = Order_ID - 1
                           ),
                    [DateTime]
            )
    RETURN
        IF (
            'Table'[Order ID] = FirstOrder_of_the_Day,
            BLANK(),
            Diff
        )

     

     

mangaus1111's avatar
mangaus1111
Solution Sage
3 years ago

Hi Adhavan ,

it is a best practice to avoid using EARLIER to make the code easier to author and maintain, using variable (VAR), like in this example:
 
Diff column =
VAR FirstOrder_of_the_Day =
    CALCULATE (
             MIN ( 'Table'[Order ID] ),
             ALLEXCEPT ( 'Table', 'Table'[Date] )
             )

VAR Order_ID = 'Table'[Order ID]

VAR Diff =
    [DateTime]
        - MAXX (
                FILTER ('Table',
                        'Table'[Order ID] = Order_ID - 1
                       ),
                [DateTime]
        )
RETURN
    IF (
        'Table'[Order ID] = FirstOrder_of_the_Day,
        BLANK(),
        Diff
    )