Reputation: 39
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
I want a desired result like this
How do I achieve this using DAX in power BI
Upvotes: 2
Views: 2445
Reputation: 1335
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:
Upvotes: 2