Sales Order Process " time for each action "

[shado26]

HI All

need your help here as im working on new project  here the EX of my table 

what i want to achive here :

1- i need to calcuted Each action time base on action name 

2- i already tried to poivt the action name but as some action duplicated time and date show error " we need to measure each action without remove the doublication 

apprecite your assist to get better way as we need to have slicer for Sale name , User name , time & order No

Orderno	CustomerName	SalesManName	UserName	ActionName	Data & Time
12345678	XXXX	SM1	SS	Quote Closed	2018-01-08 10:09:40.763
12345678	XXXX	SM1	SS	Quote Convert to Order	2018-01-08 10:09:59.543
12345678	XXXX	SM1	SS	Initiate MKD Approval	2018-01-08 10:10:24.350
12345678	XXXX	SM1	SS	DeAllocate Item	2018-01-08 12:15:36.750
12345678	XXXX	SM1	SS	DeAllocate Item	2018-01-08 12:15:36.780
12345678	XXXX	SM1	SS	Reset Order	2018-01-08 12:15:43.567
12345678	XXXX	SM1	SS	Get Approval Click	2018-01-08 12:15:49.013
12345678	XXXX	SM1	SS	Quote Convert to Order	2018-01-08 12:15:55.690
12345678	XXXX	SM1	SS	Order Push for delivery	2018-01-08 12:18:42.563
12345678	XXXX	SM1	AM	Order Rejecteded by CC	2018-01-08 12:20:11.577
12345678	XXXX	SM1	SS	Order Push for delivery	2018-01-08 12:23:03.777
12345678	XXXX	SM1	AM	Order Approved by CC	2018-01-08 12:45:37.377

[shado26]

HI @Zubair_Muhammad

attached file for your advise  

Sales Order Process time for each action

[shado26]

Hi all,

i have been tried with the below attach pbix file with another solution but i found if i have duplicated action name it will not calculate the time accurate as per the highlight blue color

moreover i need to calculate the time to show day as well not only hours 

SOP _Test

Orderno	CustomerName	SalesManName	UserName	ActionName	LogDatetime	Achive time
1111	YYYY	SM1	SS	Quote Closed	2017-01-02 14:54:53.007
1111	YYYY	SM1	SS	Quote Convert to Order	2017-01-02 14:55:31.080	0:00:38
1111	YYYY	SM1	SS	Initiate MKD Approval	2017-01-03 15:56:43.770	1:01:13
1111	YYYY	SM1	SK	Allocate Item	2017-01-10 15:24:15.847	23:27:32
1111	YYYY	SM1	SS	Order Push for delivery	2017-01-15 20:12:26.503	4:48:11
1111	YYYY	SM1	FF	Order Approved by CC	2017-01-16 11:22:50.170	15:10:24
2222	XXXX	SM1	SS	Quote Closed	2017-01-02 14:45:31.233
2222	XXXX	SM1	SS	Quote Convert to Order	2017-01-02 14:46:24.983	0:00:54
2222	XXXX	SM1	SS	Initiate MKD Approval	2017-01-02 14:48:05.797	0:01:41
2222	XXXX	SM1	GG	Allocate Item	2017-01-09 17:36:31.533	2:48:26
2222	XXXX	SM1	SS	Order Push for delivery	2017-01-09 17:59:44.030	0:23:12
2222	XXXX	SM1	SS2	Order Rejecteded by CC	2017-01-09 18:04:14.620	0:04:31
2222	XXXX	SM1	SS	Order Push for delivery	2017-01-10 15:29:37.410	21:25:23
2222	XXXX	SM1	SS2	Order Approved by CC	2017-01-10 15:35:02.697	0:05:25

[ImkeF]

Hi @shado26,

your file is unusable, because it contains references to datasources that cannot be refreshed (without access to the sources).

To help you further here, please post 2 tables here:

1) Data before transformation

2) Desired results

Please make sure that the 2 tables match: So that there are no rows in 1) that will be ignored in 2) and vice versa

[shado26]

hello @ImkeF

thank for your reply , please refer to the below table 

in last columns this what i want to see the achieve result but in power BI ,  i need to calculate Each action time base on action name for Each OrderNo and keep the duplicate action 

appreciate to advice if this can be done 

Orderno	CustomerName	SalesManName	UserName	ActionName	LogDatetime	Achive time
1111	YYYY	SM1	SS	Quote Closed	2017-01-02 14:54:53.007
1111	YYYY	SM1	SS	Quote Convert to Order	2017-01-02 14:55:31.080	0:00:38
1111	YYYY	SM1	SS	Initiate MKD Approval	2017-01-03 15:56:43.770	1:01:13
1111	YYYY	SM1	SK	Allocate Item	2017-01-10 15:24:15.847	23:27:32
1111	YYYY	SM1	SS	Order Push for delivery	2017-01-15 20:12:26.503	4:48:11
1111	YYYY	SM1	FF	Order Approved by CC	2017-01-16 11:22:50.170	15:10:24
2222	XXXX	SM1	SS	Quote Closed	2017-01-02 14:45:31.233
2222	XXXX	SM1	SS	Quote Convert to Order	2017-01-02 14:46:24.983	0:00:54
2222	XXXX	SM1	SS	Initiate MKD Approval	2017-01-02 14:48:05.797	0:01:41
2222	XXXX	SM1	GG	Allocate Item	2017-01-09 17:36:31.533	2:48:26
2222	XXXX	SM1	SS	Order Push for delivery	2017-01-09 17:59:44.030	0:23:12
2222	XXXX	SM1	SS2	Order Rejecteded by CC	2017-01-09 18:04:14.620	0:04:31
2222	XXXX	SM1	SS	Order Push for delivery	2017-01-10 15:29:37.410	21:25:23
2222	XXXX	SM1	SS2	Order Approved by CC	2017-01-10 15:35:02.697	0:05:25

[Zubair_Muhammad]

@shado26

Try this column

Column =
VAR PreviousTime =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Orderno] = EARLIER ( Table1[Orderno] )
                && Table1[LogDatetime] < EARLIER ( Table1[LogDatetime] )
        ),
        [LogDatetime], DESC
    )
RETURN
    DATEDIFF ( MINX ( PreviousTime, [LogDatetime] ), Table1[LogDatetime], SECOND )
        / 86400

[Zubair_Muhammad · Accepted solution]

@shado26

See attached file.. as well

[shado26]

Hi @Zubair_Muhammad

you are big life saver thank you alot  ,work as charm 🙂

i need 1 more help 

i used the below formula to divided the time to day/hours/min/sec 

Token Time = var days = int(SOP[Time Diff2]/24/60/60)
var hours = mod(int(SOP[Time Diff2]/60/60),24)
var minutes = mod(int(SOP[Time Diff2]/60),60)
var seconds = mod(SOP[Time Diff2],60)

return days &" days " & hours & " hours " & minutes & " minutes " & seconds & " seconds"

i need if i choose any order No to have card that calculate all time from start to End 

and if there any way to show the day as well in Time Diff 

[Zubair_Muhammad]

@shado26

Could you show the expected output/result with the sample data you posted above?

[shado26]

HI @Zubair_Muhammad

here what i want to achieve 

if choose the below order NO as Exp i need to see the total token time as below 

1 Day 18 Hours 4 Minutes 33 Seconds 

also on time Diff if we can add Day not only hours , minutes & Seconds 

[shado26]

HI ALl

1- action 

when i try to use pivot columns in Query if i have duplicated action name the time for this actoin will show as #Error

also remove columns Username & salesName to have signle line of order NO

if there any way to avoid this error as i need to see how many time this action are toke 

2- Action

even if dont remove any columns it will show #Error as well

apprecite your assist on this what the best way to finish this 

[shado26]

HI @v-danhe-msft

appreciate your quick help on this

[v-danhe-msft]

Hi @shado26,

From your description, I can’t figure out what you want to calculate, I guess you want to calculate the variety of each action time base on action name, right? If so, you can refer to below steps:

1.Create a measure to calculate.

Measure = var a=CALCULATE(MAX(Sheet1[Data & Time]),FILTER(ALL(Sheet1),Sheet1[ActionName]=MAX(Sheet1[ActionName])))

          var b=CALCULATE(MIN(Sheet1[Data & Time]),FILTER(ALL(Sheet1),Sheet1[ActionName]=MAX(Sheet1[ActionName])))

         return DATEDIFF(b,a,MINUTE)

2.Create a Slicer visual and add the [Action name] field. Create a card visual and add the [Measure] field. Now you can see the result.

If you want to slice by other columns, you just need to replace the [Action name] to the column you want to slice in before measure.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/9p5qwe5od52ca5t/Sales%20Order%20Process%20time%20for%20each%20action.pbix?...

If I misunderstood what you mean, please clarify what you want to calculate.

Regards,

Daniel He

[shado26]

HI @v-danhe-msft

its nice to get touch with you again 

yes you correct but what i want to achive as i have many Sales order Number so i need to calculate each step for each sales order

we will add Slicer for Sales Name , User Name , and Time ragne but this is easy no issue with that 

what is really an issue we need to calculate each step can you help me with that ???

Orderno	CustomerName	SalesManName	UserName	ActionName		Data & Time	Achive time
12345678	XXXX	SM1	SS	Quote Closed	Action 1	2018-01-08 10:09:40.763
12345678	XXXX	SM1	SS	Quote Convert to Order	Action 2	2018-01-08 10:09:59.543	0:00:19
12345678	XXXX	SM1	SS	Initiate MKD Approval	Action 3	2018-01-08 10:10:24.350	0:00:25
12345678	XXXX	SM1	SS	DeAllocate Item	Action 4	2018-01-08 12:15:36.750	2:05:12
12345678	XXXX	SM1	SS	DeAllocate Item	Action 4	2018-01-08 12:15:36.780	0:00:00
12345678	XXXX	SM1	SS	Reset Order	Action 5	2018-01-08 12:15:43.567	0:00:07
12345678	XXXX	SM1	SS	Get Approval Click	Action 6	2018-01-08 12:15:49.013	0:00:05
12345678	XXXX	SM1	SS	Quote Convert to Order	Action 2	2018-01-08 12:15:55.690	0:00:07
12345678	XXXX	SM1	SS	Order Push for delivery	Action 7	2018-01-08 12:18:42.563	0:02:47
12345678	XXXX	SM1	AM	Order Rejecteded by CC	Action 8	2018-01-08 12:20:11.577	0:01:29
12345678	XXXX	SM1	SS	Order Push for delivery	Action 7	2018-01-08 12:23:03.777	0:02:52
12345678	XXXX	SM1	AM	Order Approved by CC	Action 9	2018-01-08 12:45:37.377	0:22:34