Calculate Average Number of Days Between Multiple Dates with dax in power bi

1
votes

Let's say I have the following data frame. I want to calculate the average number of days between all the activities for a particular account using Dax in power bi

Let say I have this: enter image description here

I want a desired result like this enter image description here

How do I achieve this using DAX in power BI

1
powerbidax

1 Answers

1
votes

Assuming you have the data in a table as in your picture, create a calculated column like this:

AvgerageDaysInbetween = 
var thisCustomer = [Customer]
var temp = 
SUMX(
    FILTER(
        SUMMARIZE(
            'Table';
            [Customer];
            "avg";  
                DIVIDE(
                    DATEDIFF(MIN('Table'[DateOfOrder]); MAX('Table'[DateOfOrder]); DAY);
                    DISTINCTCOUNT('Table'[DateOfOrder])-1;
                    0
            )
        );
        [Customer] = thisCustomer
    );
    [avg]
)
return
temp

Resulting table:

enter image description here