Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to 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 )
Why does your solution minus 1 from the number of orders?
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!
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 )
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |