Forum Discussion

Milan_TBC's avatar
Milan_TBC
Frequent Visitor
3 years ago
Solved

Average Customer Return Time

Hi, I want to calculate the average return time of my customers over time.  I have a table with general data such as Cust_ID, and order_date per order.  Right now i have general calculations bas...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi @Manal_Edd ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Create two measures as below

    Datediff = 
VAR _selcust =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _seldate =
    SELECTEDVALUE ( 'Table'[closed_at] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[closed_at] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[lead_customer_id] = _selcust
                && 'Table'[closed_at] < _seldate
        )
    )
RETURN
    DATEDIFF ( _predate, _seldate, DAY )
    average_orderTime = 
VAR _selcustid =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[lead_customer_id] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid )
    )
VAR _sum =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid ),
        [Datediff]
    )
RETURN
    IF (
        ISBLANK ( [Datediff] ),
        0,
        IF ( _count = 1, _sum, ROUND ( DIVIDE ( _sum, _count - 1 ), 0 ) )
    )

    2. Create a table visual 

     

    If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

Milan_TBC's avatar
Milan_TBC
Frequent Visitor
3 years ago

 

The following table is an indication for what is needed, where the yellow values are what i'm looking for!

 

amitchandak Right now your solution does not seem to work, i recieve syntax errors, mainly "< earlier({order date]) )" does not seem to function correctly.

 

Looking forward to any other solutions, Thanks in advance!

  • Milan_TBC's avatar
    Milan_TBC
    Frequent Visitor
    3 years ago

    Addendum;

     

    average_orderTime also has to be yellow, this value is not calculated right now!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi @Manal_Edd ,

      I created a sample pbix file(see attachment) for you, please check whether that is what you want.

      1. Create two measures as below

      Datediff = 
VAR _selcust =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _seldate =
    SELECTEDVALUE ( 'Table'[closed_at] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[closed_at] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[lead_customer_id] = _selcust
                && 'Table'[closed_at] < _seldate
        )
    )
RETURN
    DATEDIFF ( _predate, _seldate, DAY )
      average_orderTime = 
VAR _selcustid =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[lead_customer_id] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid )
    )
VAR _sum =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid ),
        [Datediff]
    )
RETURN
    IF (
        ISBLANK ( [Datediff] ),
        0,
        IF ( _count = 1, _sum, ROUND ( DIVIDE ( _sum, _count - 1 ), 0 ) )
    )

      2. Create a table visual 

       

      If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

      How to upload PBI in Community

      Best Regards

      • Milan_TBC's avatar
        Milan_TBC
        Frequent Visitor
        3 years ago

        Thanks a lot!