DAX : Find process time & last operation from date/time difference

up vote
0
down vote

favorite

Need your help to find "Process Time" in hours and where it in last operation based on Max Operation number

DATEDIFF(

    CALCULATE(

        SUM(tableX[date/time]),

        ALLEXCEPT(tableX,tableX[Operation],tableX[ID]),

        tableX[date/time] <= EARLIER(tableX[date/time])

   ), 

   tableX[date/time],HOUR

)

capture

asked Nov 8 at 3:53

zzz-zzz

123

add a comment |

up vote
0
down vote

favorite

Need your help to find "Process Time" in hours and where it in last operation based on Max Operation number

DATEDIFF(

    CALCULATE(

        SUM(tableX[date/time]),

        ALLEXCEPT(tableX,tableX[Operation],tableX[ID]),

        tableX[date/time] <= EARLIER(tableX[date/time])

   ), 

   tableX[date/time],HOUR

)

capture

asked Nov 8 at 3:53

zzz-zzz

123

add a comment |

up vote
0
down vote

favorite

Need your help to find "Process Time" in hours and where it in last operation based on Max Operation number

DATEDIFF(

    CALCULATE(

        SUM(tableX[date/time]),

        ALLEXCEPT(tableX,tableX[Operation],tableX[ID]),

        tableX[date/time] <= EARLIER(tableX[date/time])

   ), 

   tableX[date/time],HOUR

)

capture

asked Nov 8 at 3:53

zzz-zzz

123

Need your help to find "Process Time" in hours and where it in last operation based on Max Operation number

DATEDIFF(

    CALCULATE(

        SUM(tableX[date/time]),

        ALLEXCEPT(tableX,tableX[Operation],tableX[ID]),

        tableX[date/time] <= EARLIER(tableX[date/time])

   ), 

   tableX[date/time],HOUR

)

capture

DATEDIFF(

    CALCULATE(

        SUM(tableX[date/time]),

        ALLEXCEPT(tableX,tableX[Operation],tableX[ID]),

        tableX[date/time] <= EARLIER(tableX[date/time])

   ), 

   tableX[date/time],HOUR

)

DATEDIFF(

    CALCULATE(

        SUM(tableX[date/time]),

        ALLEXCEPT(tableX,tableX[Operation],tableX[ID]),

        tableX[date/time] <= EARLIER(tableX[date/time])

   ), 

   tableX[date/time],HOUR

)

excel powerbi dax powerpivot

asked Nov 8 at 3:53

zzz-zzz

123

asked Nov 8 at 3:53

zzz-zzz

123

asked Nov 8 at 3:53

zzz-zzz

123

asked Nov 8 at 3:53

zzz-zzz

123

asked Nov 8 at 3:53

zzz-zzz

123

add a comment |

1 Answer
1

active

oldest

votes

up vote
1
down vote

accepted

I think you are looking for the following calculated column:

Process Time (Hours) = DATEDIFF(

    CALCULATE(

        MAX('tableX'[Date/Time]),

        ALLEXCEPT(tableX,'tableX'[ID]),

        'tableX'[date/time] < EARLIER('tableX'[date/time])

   ), 

   'tableX'[Date/Time],HOUR

)

This expression calculates the elapsed time sinds the previous step in the operation. If you want to calculate the elapsed time sinds the start of the operation, then simply change MAX('tableX'[Date/Time]) to MIN('tableX'[Date/Time]). Like this:

enter image description here

To create the last column, you can use this:

Last Operation =

IF (

    'tableX'[Date/Time]

        = CALCULATE ( MAX ( 'tableX'[Date/Time] ), ALLEXCEPT ( 'tableX', tableX[ID] ) ),

    "Y",

    "N"

)

edited Nov 8 at 9:25

answered Nov 8 at 9:16

Marco Vos

1,854149

Ah it works perfectly !!! thank you and wondering if you could answer for the 2nd question how to create new model to mark last operation for example 30 should be the last operation for wall and and 40 for floor .
– zzz-zzz
Nov 8 at 9:22

I presume you mean the other column in your example. (See my edit). If not, what do you mean with "40 for floor" ?
– Marco Vos
Nov 8 at 9:27

it works like a charm, thanks a lot !
– zzz-zzz
Nov 8 at 9:35

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53201315%2fdax-find-process-time-last-operation-from-date-time-difference%23new-answer', 'question_page');
}
);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
1
down vote

accepted

I think you are looking for the following calculated column:

Process Time (Hours) = DATEDIFF(

    CALCULATE(

        MAX('tableX'[Date/Time]),

        ALLEXCEPT(tableX,'tableX'[ID]),

        'tableX'[date/time] < EARLIER('tableX'[date/time])

   ), 

   'tableX'[Date/Time],HOUR

)

enter image description here

To create the last column, you can use this:

Last Operation =

IF (

    'tableX'[Date/Time]

        = CALCULATE ( MAX ( 'tableX'[Date/Time] ), ALLEXCEPT ( 'tableX', tableX[ID] ) ),

    "Y",

    "N"

)

edited Nov 8 at 9:25

answered Nov 8 at 9:16

Marco Vos

1,854149

Ah it works perfectly !!! thank you and wondering if you could answer for the 2nd question how to create new model to mark last operation for example 30 should be the last operation for wall and and 40 for floor .
– zzz-zzz
Nov 8 at 9:22

I presume you mean the other column in your example. (See my edit). If not, what do you mean with "40 for floor" ?
– Marco Vos
Nov 8 at 9:27

it works like a charm, thanks a lot !
– zzz-zzz
Nov 8 at 9:35

add a comment |

up vote
1
down vote

accepted

I think you are looking for the following calculated column:

Process Time (Hours) = DATEDIFF(

    CALCULATE(

        MAX('tableX'[Date/Time]),

        ALLEXCEPT(tableX,'tableX'[ID]),

        'tableX'[date/time] < EARLIER('tableX'[date/time])

   ), 

   'tableX'[Date/Time],HOUR

)

enter image description here

To create the last column, you can use this:

Last Operation =

IF (

    'tableX'[Date/Time]

        = CALCULATE ( MAX ( 'tableX'[Date/Time] ), ALLEXCEPT ( 'tableX', tableX[ID] ) ),

    "Y",

    "N"

)

edited Nov 8 at 9:25

answered Nov 8 at 9:16

Marco Vos

1,854149

Ah it works perfectly !!! thank you and wondering if you could answer for the 2nd question how to create new model to mark last operation for example 30 should be the last operation for wall and and 40 for floor .
– zzz-zzz
Nov 8 at 9:22

I presume you mean the other column in your example. (See my edit). If not, what do you mean with "40 for floor" ?
– Marco Vos
Nov 8 at 9:27

it works like a charm, thanks a lot !
– zzz-zzz
Nov 8 at 9:35

add a comment |

up vote
1
down vote

accepted

I think you are looking for the following calculated column:

Process Time (Hours) = DATEDIFF(

    CALCULATE(

        MAX('tableX'[Date/Time]),

        ALLEXCEPT(tableX,'tableX'[ID]),

        'tableX'[date/time] < EARLIER('tableX'[date/time])

   ), 

   'tableX'[Date/Time],HOUR

)

enter image description here

To create the last column, you can use this:

Last Operation =

IF (

    'tableX'[Date/Time]

        = CALCULATE ( MAX ( 'tableX'[Date/Time] ), ALLEXCEPT ( 'tableX', tableX[ID] ) ),

    "Y",

    "N"

)

edited Nov 8 at 9:25

answered Nov 8 at 9:16

Marco Vos

1,854149

I think you are looking for the following calculated column:

Process Time (Hours) = DATEDIFF(

    CALCULATE(

        MAX('tableX'[Date/Time]),

        ALLEXCEPT(tableX,'tableX'[ID]),

        'tableX'[date/time] < EARLIER('tableX'[date/time])

   ), 

   'tableX'[Date/Time],HOUR

)

enter image description here

To create the last column, you can use this:

Last Operation =

IF (

    'tableX'[Date/Time]

        = CALCULATE ( MAX ( 'tableX'[Date/Time] ), ALLEXCEPT ( 'tableX', tableX[ID] ) ),

    "Y",

    "N"

)

edited Nov 8 at 9:25

answered Nov 8 at 9:16

Marco Vos

1,854149

edited Nov 8 at 9:25

answered Nov 8 at 9:16

Marco Vos

1,854149

answered Nov 8 at 9:16

Marco Vos

1,854149

answered Nov 8 at 9:16

Marco Vos

1,854149

Ah it works perfectly !!! thank you and wondering if you could answer for the 2nd question how to create new model to mark last operation for example 30 should be the last operation for wall and and 40 for floor .
– zzz-zzz
Nov 8 at 9:22

I presume you mean the other column in your example. (See my edit). If not, what do you mean with "40 for floor" ?
– Marco Vos
Nov 8 at 9:27

it works like a charm, thanks a lot !
– zzz-zzz
Nov 8 at 9:35

add a comment |

Ah it works perfectly !!! thank you and wondering if you could answer for the 2nd question how to create new model to mark last operation for example 30 should be the last operation for wall and and 40 for floor .
– zzz-zzz
Nov 8 at 9:22

I presume you mean the other column in your example. (See my edit). If not, what do you mean with "40 for floor" ?
– Marco Vos
Nov 8 at 9:27

it works like a charm, thanks a lot !
– zzz-zzz
Nov 8 at 9:35

Ah it works perfectly !!! thank you and wondering if you could answer for the 2nd question how to create new model to mark last operation for example 30 should be the last operation for wall and and 40 for floor .
– zzz-zzz
Nov 8 at 9:22

I presume you mean the other column in your example. (See my edit). If not, what do you mean with "40 for floor" ?
– Marco Vos
Nov 8 at 9:27

it works like a charm, thanks a lot !
– zzz-zzz
Nov 8 at 9:35

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Name

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Agfdhyk