calculated column for frequency of dates

[jeanseb]

Hello, I am new on this platform and I need help concerning Power BI!

I have a table named Orders, which indicate, for each order, the user name and the date of the order. On the table Users, I would like to create a calculated column named "frequency of orders" which would calculate the frequency of orders for each user.

for example, user3 has made 2 orders, at 7 days of difference, so the frequency should be equal to 7.

Could you help me for that ?

Thanks a lot,

[Jbmolina93]

Why does your solution minus 1 from the number of orders?

[Sean]

2 Calculated COLUMNS

number of orders =
CALCULATE (
    COUNTROWS ( Orders ),
    FILTER ( Orders, Orders[user_name] = Users[user_name] )
)

frequency of orders =
DIVIDE (
    DATEDIFF (
        FIRSTDATE ( Orders[created_at] ),
        LASTDATE ( Orders[created_at] ),
        DAY
    ),
    Users[number of orders] - 1,
    0
)

and the result

Hope this helps!

[jeanseb · Accepted solution]

Thanks you so much for your help!

In fact with your formula for "frequency of orders", the results for my file is always 0... I have created columns "first date" and "last date" and "frequency bis" and now it works well! 

Thanks you again!!!

first date = 
CALCULATE(
    FIRSTDATE ( Orders[created_at] );
    FILTER(Orders ; Orders[user_name] = Users[user_name])
)

last date = 
CALCULATE(
    LASTDATE( Orders[created_at] );
    FILTER(Orders ; Orders[user_name] = Users[user_name])
)

frequency bis = 
DIVIDE (
    DATEDIFF (
        Users[first date];
        Users[last date];
        DAY
    );
    Users[number of orders] - 1;
    0
)