cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculating Orders Frequency

Hi,

i`m quite new to PowerBi and I need some help...

I have a database with orders, with info of CUSTOMER, ORDER.DATE, ORDERS.QTY., and ITEM.ID. What I`m trying to achieve here is: figure it out the frequency each customer buy, including what items and their quantities...

For example, I`d like to know that `Customer A` buy every two weeks, or 13 days (on average), and buy 3 units of product X and 2 units of products Y (on average as well).

Can anyone help me out?? Thanks !!

1 ACCEPTED SOLUTION
Solution Sage

Hi @chernni,

For the two questions, they all based on how to make groups.

1. Frequency of order. Add 'Orders Fre'[Customer] = EARLIER ( 'Orders Fre'[Customer] ) inside the Filter to make the formula be based on Customer group. Then it will get the next date within the same customer group not the next physical row.

```Frequency of order =
DATEDIFF (
CALCULATE (
MAX ( 'Orders Fre'[Date] ),
FILTER (
'Orders Fre',
'Orders Fre'[Date] < EARLIER ( 'Orders Fre'[Date] )
&& 'Orders Fre'[Customer] = EARLIER ( 'Orders Fre'[Customer] )
)
),
'Orders Fre'[Date],
DAY
)```

2. Frequent items. Same issue. In my prior expression I'm using ALLEXCEPT ( 'Orders Fr', 'Orders Fr'[Item.ID] ) to make the formula be based on only Item.ID group. That's why all the same Item.ID got the same percentage. So to resolve your issue, add one more condition in ALLEXCEPT().

```Frequent items =
DIVIDE (
CALCULATE (
COUNT ( 'Orders Fre'[Item.ID] ),
ALLEXCEPT ( 'Orders Fre', 'Orders Fre'[Item.ID], 'Orders Fre'[Customer] )
),
DISTINCTCOUNT ( 'Orders Frequency'[Date] )
)```

Little tips: the most important point in your requirement is to make groups for your data. And generally in DAX, we can use EARLIER() or ALLEXCEPT() function to ahieve this. EARLIER() is used in calculated column and ALLEXCEPT() can be use in both measure and calculated column.

I think I have shown you the right direction. Please make more effort and try to tune the formula on yourself.

Thanks,
Xi Jin.

17 REPLIES 17
New Member

sorry I can not apply this, can you check what it might be wrong from the formula? i whant to calculate the frequency in month.

Frecuency of orders = DATEDIFF(CALCULATE(MAX(qry_KAIROS[Date], FILTER(qry_KAIROS, qry_KAIROS[Date] < EARLIER (qry_KAIROS[Date]) && qry_KAIROS[Customer] = EARLIER( qry_KAIROS[Customer]))),qry_KAIROS[Date], MONTH))

Super User

Hi,

Share a sample dataset and for that sample show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi @Ashish_Mathur, thanks for your response. Check below the request:

In the image below we have a simple example with a single customer "JDS", with two orders.

Order 1: Jan 11st, including two products (ITEM.ID), one unit each.

Order 2: Feb 23rd, including three products (ITEM.ID), one unit each.

As a result I'd like something like:

Frequency of order: (Feb 23rd) - (Jan 11st) = 43 days.

Frequent items: 3543 (100% of orders), 3898 (100% of orders) and 3912 (50%) of orders.

Something like that.

Another example, with three orders:

As a result here, I'd have as a result:

Frequency of order: (38+82)/2= 60 days (details on image below)

Frequent items: 1201 (100% of orders).

Hope i made myself clear... If not, please let me know! thanks for the support!!!

Btw, if needed I can give more complex examples, with more orders, or multiple items, but my goal is to identify the average frequency of order and average items requested by a given customer.

Thanks!!!

Solution Sage

Hi @chernni,

To achieve your requirement, you can refer to following method:

We can create calculated column with Earlier() function to get the prior row value as in your scenario, to get the prior date value. So the Frequency of order expression can be:

```Frequency of order =
DATEDIFF (
CALCULATE (
MAX ( 'Orders Fr'[Date] ),
FILTER ( 'Orders Fr', 'Orders Fr'[Date] < EARLIER ( 'Orders Fr'[Date] ) )
),
'Orders Fr'[Date],
DAY
)```

Then the expression of Frequent items can be:

```Frequent items =
CALCULATE (
COUNT ( 'Orders Fr'[Item.ID] ),
ALLEXCEPT ( 'Orders Fr', 'Orders Fr'[Item.ID] )
)
/ DISTINCTCOUNT ( 'Orders Fr'[Date] )```

The result shows like:

Thanks,
Xi Jin.

Helper V

This is Not working , How you make 👍 ?

Frequent Visitor

@v-xjiin-msft Thanks for the solution... but i`m struggling to understanding it.

I cannot use the function EARLIER properly, can you please share the power bi report that you`ve took the screen shot below? it might be easier for me to understand... tks!

Solution Sage

Hi @chernni,

Sure, check Page2 in my shared report.

https://1drv.ms/u/s!AlqSnZZUVHmsg3ZnKpzvcwZ5HeSH

Thanks,
Xi Jin.

Helper V

Hi @chernni  The pbix file is not available at the cloud drive you mentioned.

Frequent Visitor

@v-xjiin-msft thank you so much for you patience! but unfortunately, I cannot see the desired solution...

I can`t apply the `RANK ORDER` nor `FREQUENCY OF ORDER` measures... as images below...

For the `FREQUENT ITEMS` formula, i used the following:

Frequent items = CALCULATE(COUNT('db ORDERS'[ITEM.ID]),ALLEXCEPT('db ORDERS','db ORDERS'[ITEM.ID]))/DISTINCTCOUNT('db ORDERS'[ORDER.DATE])

but the result is a bunch of `infinity` for each row of my table... as the image below...

Solution Sage

Hi @chernni,

=> I can`t apply the `RANK ORDER` nor `FREQUENCY OF ORDER` measures... as images below...

You are using measure. Right? As I said before, I'm using calculated column not measure. Generally, earlier() function is used in calculated column. Same to Rankx. So to resolve your issue, you just need to change the measure to calculated column.

Then for your Frequent items returns infinity. It seems like there exists 0 value in DISTINCTCOUNT('db ORDERS'[ORDER.DATE]). Did you apply any filter on your table?

To troubleshoot your issue, you can separate the formula to two parts. See if there exists 0 values. And still use calculated column instead of measure. Also you can try Divide() function.

Thanks,
Xi Jin.

Frequent Visitor

@v-xjiin-msft awesome! it worked! But it might not be exactly what i need... What`s showing me in the image below is the following: `Frequency of order` shows me the difference, in days, from the last order indepently of the customer which can be a valuable information as well. But what i`m actually looking for is, the difference, in days, between orders from the same customer.

Let`s take as an example customer `FABRICA`, the line from Jan 15th. What i`d like to see in the `Frequency of order` would be jan 15 - Jan 9 = 6 days (instead of Jan 15 - Jan 11 = 4 days). Is it possible to do something like that?

Also, it appears the percentages in the `Frequent items` columns are global... related to the whole database... take as an example ITEM.ID = 3898, in every line is 87.32%, as per my understanding it means in 87.32% of the orders this item goes into. Which is a nice info, but i`m also looking for the info by customer... For customer A it can be 50%, customer B 100%, C 90% and from that on...

Solution Sage

Hi @chernni,

For the two questions, they all based on how to make groups.

1. Frequency of order. Add 'Orders Fre'[Customer] = EARLIER ( 'Orders Fre'[Customer] ) inside the Filter to make the formula be based on Customer group. Then it will get the next date within the same customer group not the next physical row.

```Frequency of order =
DATEDIFF (
CALCULATE (
MAX ( 'Orders Fre'[Date] ),
FILTER (
'Orders Fre',
'Orders Fre'[Date] < EARLIER ( 'Orders Fre'[Date] )
&& 'Orders Fre'[Customer] = EARLIER ( 'Orders Fre'[Customer] )
)
),
'Orders Fre'[Date],
DAY
)```

2. Frequent items. Same issue. In my prior expression I'm using ALLEXCEPT ( 'Orders Fr', 'Orders Fr'[Item.ID] ) to make the formula be based on only Item.ID group. That's why all the same Item.ID got the same percentage. So to resolve your issue, add one more condition in ALLEXCEPT().

```Frequent items =
DIVIDE (
CALCULATE (
COUNT ( 'Orders Fre'[Item.ID] ),
ALLEXCEPT ( 'Orders Fre', 'Orders Fre'[Item.ID], 'Orders Fre'[Customer] )
),
DISTINCTCOUNT ( 'Orders Frequency'[Date] )
)```

Little tips: the most important point in your requirement is to make groups for your data. And generally in DAX, we can use EARLIER() or ALLEXCEPT() function to ahieve this. EARLIER() is used in calculated column and ALLEXCEPT() can be use in both measure and calculated column.

I think I have shown you the right direction. Please make more effort and try to tune the formula on yourself.

Thanks,
Xi Jin.

Helper V

hi @v-xjiin-msft  This Not working

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors