topic Re: CALCLATE TIME BETWEN INTERVALS in DAX Commands and Tips

CALCLATE TIME BETWEN INTERVALS

MAZHAR — Thu, 06 Jul 2023 10:57:05 GMT

I have a large Excel sheet like this where Document Numbers will be listed along with all other information as in the picture. I want to calculate the following.

Total Time (Minutes) = (START-END)
Break Time (Minutes) = (RESUME- PP)

Re: CALCLATE TIME BETWEN INTERVALS

tamerj1 — Thu, 06 Jul 2023 12:44:06 GMT

Hi MAZHAR
How many poses there could be? Or just once?

Re: CALCLATE TIME BETWEN INTERVALS

MAZHAR — Thu, 06 Jul 2023 14:40:07 GMT

tamerj1 Thanks for your reply it is 5 the moment I just share an example of one.

PP
BRK
MSC
1x2x

Re: CALCLATE TIME BETWEN INTERVALS

MAZHAR — Fri, 07 Jul 2023 06:42:04 GMT

I tried the following solution which is not giving me the exact total time on one Document Number, it gives me accurate time on the last level which is SCP_LINE but on combining all it fails.

I created three measures to calculate Total Time

START TIME = CALCULATE(MAX(MCH_WorkStatusD[Time]), MCH_WorkStatusD[CONDITION] = "START")

END TIME = CALCULATE(MAX(MCH_WorkStatusD[Time]), MCH_WorkStatusD[CONDITION] = "END")

STAGE = DATEDIFF([START TIME],[END TIME],MINUTE)

Re: CALCLATE TIME BETWEN INTERVALS

Anonymous — Mon, 10 Jul 2023 07:33:16 GMT

Hi MAZHAR ,

I suggest you to create virtual table in your calculation and then use SUMX to achieve your goal.

Total Time = VAR _SUMMARIZE = SUMMARIZE ( 'MCH_WorkStatusD', MCH_WorkStatusD[Line], MCH_WorkStatusD[SCP_Line], "Diff", VAR _STATR = CALCULATE ( MAX ( MCH_WorkStatusD[Time] ), MCH_WorkStatusD[CONDITION] = "START" ) VAR _END = CALCULATE ( MAX ( MCH_WorkStatusD[Time] ), MCH_WorkStatusD[CONDITION] = "END" ) RETURN DATEDIFF ( _STATR, _END, MINUTE ) ) RETURN SUMX ( _SUMMARIZE, [Diff] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.