Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Retrieve earlier column from previous orders and put into column

I have one or more orders which are in the future, and I would like to add a column to the table of orders with information from previous similar orders which have been done as an indication on what to excpect. i.e. travel time.

 

An order in the order table has the following columns: See example table in the bottom.

 

Explanation:

  1. How do i identify previous orders?
    1. By the fields Customer No, Type of order and Status of order.
  2. What field is it i like to fetch?
    1. Travel time.
  3. Where should it be stored?
    1. In its own column named Estimated_Travel_Hours if average or Previous_Travel_Hours if last known value
  4. Known limitations or comments?
    1. An order might not have a privous order as it might be the first which is fine. Set it to 0.
    2. There may be one ore more orders in the futere, but not nessessary anyone done yet. See pont 4.1.
    3. There may be several orders previously done. Solve this by either
      1. Make an average of them all or
      2. Take the last value
  5. Short explanation on what I like the column to do:
    1. Take the customer number from a order with status 0, and find previous orders with the same customer number, order type 12 and status 0 and retrieve the column Travel time. Average if many.

What I have manage so far thanks to the forum:

Previous_Travel =
CALCULATE(
FIRSTNONBLANK(
'HM Ordre'[Actual_Travel_Hours_HM];1
);
FILTER('HM Ordre';'HM Ordre'[KundeNr]=
EARLIER('HM Ordre'[KundeNr])&&'HM Ordre'[Value_IDBaksystem]=
EARLIER('HM Ordre'[Value_IDBaksystem])
-1
)
)
This does not take into consideration previous order types or if it is done, which means it more often is blank.

 

Explanation

Unique order no

Unique Customer no

What type of order

Travel time

Status of order

SQL Name Table and Column

'HM Ordre'[VB Order no]

'HM Ordre'[KundeNr]

'HM Ordre'[OrdreType]

'HM Ordre'[Actual_Travel_Hours_HM]

'HM Ordre'[Status]

Example order in future

79315

2575

12

 

0

Example order done

60604

130

12

3,5

3

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I found the solution myself:

 

Previous_Average_Travel_Hours =
CALCULATE(
AVERAGE(
'HM Ordre'[Actual_Travel_Hours_HM]
);
FILTER(
'HM Ordre';
'HM Ordre'[KundeNr]=EARLIER('HM Ordre'[KundeNr])
);
FILTER(
'HM Ordre';
'HM Ordre'[Status]=3
);
FILTER(
'HM Ordre';
'HM Ordre'[OrdreType]=12
)
)

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Assume there is a column like [value id] in my table to identify the index of orders as in ascending order.

For example, for customer no=1, value id=2 is the feature order, value id=1 is the previous order

(they have the same value for [type of order] and [status])

value id order no customer no type of order travel time status
1 1 1 12 2 0
2 2 1 12   0

Then i can create measures

sum per customer = 
CALCULATE(SUM(Sheet1[travel time]),FILTER(ALLEXCEPT(Sheet1,Sheet1[customer no],Sheet1[type of order],Sheet1[status]),Sheet1[value id]<MAX(Sheet1[value id])))

feature id = CALCULATE(MAX(Sheet1[value id]),ALLEXCEPT(Sheet1,Sheet1[customer no],Sheet1[type of order],Sheet1[status]))

count rows = CALCULATE(COUNT(Sheet1[value id]),ALLEXCEPT(Sheet1,Sheet1[customer no],Sheet1[type of order],Sheet1[status]))

total time = IF(SUM(Sheet1[value id])<>[feature id]||[count rows]=1,0,[sum per customer])

final output = IF([count rows]>2,[total time]/([count rows]-1),[total time])
2.png

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you for your suggestion!

 

I see I need to clarify som parameters.

  1. I do not have an id that identifiy it as a future order or not. A higher order number or Value_Id, (which I also have in the dattabase), could be set to done before a lower numbered one. The only way to identify a previous order is by the status = 3.
  2. There may also be other Order Types than 12. For example 10 or 11, which I do not whis to take into account, which I pressume, if I understand it correctly, you mean by "(they have the same value for [type of order] and [status])"?

How would you solve it then?

Anonymous
Not applicable

I found the solution myself:

 

Previous_Average_Travel_Hours =
CALCULATE(
AVERAGE(
'HM Ordre'[Actual_Travel_Hours_HM]
);
FILTER(
'HM Ordre';
'HM Ordre'[KundeNr]=EARLIER('HM Ordre'[KundeNr])
);
FILTER(
'HM Ordre';
'HM Ordre'[Status]=3
);
FILTER(
'HM Ordre';
'HM Ordre'[OrdreType]=12
)
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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