topic Re: Calculate minute between two times interval with logical value in DAX Commands and Tips

Calculate minute between two times interval with logical value

lifesafari — Mon, 12 Feb 2024 14:53:50 GMT

Hello,

i have product registration and i have two columns (Stard / End) with time (HH:MM:SS).

Consideratin i working on 3 tourn:

morning 06.00.00 - 13.59.59

afternoon 14.00.00 - 21.59.59

night 22.00.00 - 05.59.59<

and some time are between tiw turn (ex start 13.50 and 14.10) i need to calculate the effective working time on morning, afternood and night if value on column "Tiype" have value = 2.
For example 13.50 - 14.10 -> 10 minutes on morning and 10 minutes on afternoon.

I try to do this but not working 😞

DurataEffettiva =
IF(
LavMacchina[Tipo] = 2,
IF(
HOUR(LavMacchina[Inizio]) >= 6 && HOUR(LavMacchina[Fine]) <= 13,
MINUTE(LavMacchina[Fine]) - MINUTE(LavMacchina[Inizio]),
IF(
HOUR(LavMacchina[Inizio]) < 6,
MINUTE(DATEADD(LavMacchina[Inizio], 6, HOUR)) + MINUTE(LavMacchina[Fine]) - MINUTE(DATEADD(LavMacchina[Fine], 14, HOUR)),
MINUTE(LavMacchina[Fine]) - MINUTE(DATEADD(LavMacchina[Inizio], 6, HOUR))
)
),
MINUTE(LavMacchina[Fine]) - MINUTE(LavMacchina[Inizio])
)

Also try this

2Mattina =

SUMX(

FILTER(

LavMacchina,

LavMacchina[Tipo] = 2 &&

HOUR(LavMacchina[Inizio]) >= 6 &&

HOUR(LavMacchina[Fine]) <= 14 &&

LavMacchina[Fine] < TIME(14, 0, 0)

MINUTE(LavMacchina[Fine]) - MINUTE(LavMacchina[Inizio])

)

Re: Calculate minute between two times interval with logical value

lbendlin — Tue, 13 Feb 2024 00:44:10 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculate minute between two times interval with logical value

lifesafari — Tue, 13 Feb 2024 13:34:02 GMT

Hello, thanks for reply.

This is an example of my dataset:

Type	Desc	MachineCode	Start	End	Minutes	% Equipment
3	Fermo Tec. > 10 min	9981	02/12/2023 13:31	02/12/2023 14:10	45,00	1,00
278	Fermata Programmata	9981	02/12/2023 12:45	02/12/2023 13:30	45,00	1,00
1	Inizio Lavoro	9981	02/12/2023 12:32	02/12/2023 12:44	13,00	1,00
2	Attrezzaggio Regolazioni	9981	02/12/2023 11:40	02/12/2023 12:31	52,00	1,00
3	Fermo Tec. > 10 min	9981	02/12/2023 09:40	02/12/2023 11:39	120,00	1,00
1	Inizio Lavoro	9981	02/12/2023 08:35	02/12/2023 09:39	65,00	1,00
274	Ri-attrezzaggio	9981	02/12/2023 07:50	02/12/2023 08:34	45,00	1,00
3	Fermo Tec. > 10 min	9981	02/12/2023 07:05	02/12/2023 07:49	45,00	1,00
1	Inizio Lavoro	9981	02/12/2023 06:00	02/12/2023 07:04	65,00	1,00
1	Inizio Lavoro	9981	02/12/2023 05:37	02/12/2023 05:59	23,00	1,00
2	Attrezzaggio Regolazioni	9981	02/12/2023 04:27	02/12/2023 05:36	70,00	1,00
1	Inizio Lavoro	9981	02/12/2023 03:46	02/12/2023 04:26	41,00	1,00
2	Attrezzaggio Regolazioni	9981	02/12/2023 03:01	02/12/2023 03:45	45,00	1,00

This is what i expected

Type	Desc	MachineCode	Start	End	Minutes	% Equipment	Tourn	MinutTourn	Tourn	MinutTourn
3	Fermo Tec. > 10 min	9981	02/12/2023 13:31	02/12/2023 14:10	45,00	1,00	Morning	29	Night	10
278	Fermata Programmata	9981	02/12/2023 12:45	02/12/2023 13:30	45,00	1,00	Morning	45
1	Inizio Lavoro	9981	02/12/2023 12:32	02/12/2023 12:44	13,00	1,00	Morning	13
2	Attrezzaggio Regolazioni	9981	02/12/2023 11:40	02/12/2023 12:31	52,00	1,00	Morning	52
3	Fermo Tec. > 10 min	9981	02/12/2023 09:40	02/12/2023 11:39	120,00	1,00	Morning	120
1	Inizio Lavoro	9981	02/12/2023 08:35	02/12/2023 09:39	65,00	1,00	Morning	65
274	Ri-attrezzaggio	9981	02/12/2023 07:50	02/12/2023 08:34	45,00	1,00	Morning	45
3	Fermo Tec. > 10 min	9981	02/12/2023 07:05	02/12/2023 07:49	45,00	1,00	Morning	45
1	Inizio Lavoro	9981	02/12/2023 06:00	02/12/2023 07:04	65,00	1,00	Morning	65
1	Inizio Lavoro	9981	02/12/2023 05:37	02/12/2023 05:59	23,00	1,00	Night	23
2	Attrezzaggio Regolazioni	9981	02/12/2023 04:27	02/12/2023 05:36	70,00	1,00	Night	70
1	Inizio Lavoro	9981	02/12/2023 03:46	02/12/2023 04:26	41,00	1,00	Night	41
2	Attrezzaggio Regolazioni	9981	02/12/2023 03:01	02/12/2023 03:45	45,00	1,00	Night	45

Re: Calculate minute between two times interval with logical value

lifesafari — Fri, 16 Feb 2024 06:27:03 GMT

Hello, this is my xls file https://docs.google.com/spreadsheets/d/1qQtoVbLJfnyV4dWqerVB_ADgGaIGVAOx_ypHVMBhU7c/edit?usp=drive_link

Re: Calculate minute between two times interval with logical value

lbendlin — Sat, 17 Feb 2024 01:40:28 GMT

Please check the link, it requires access.

Re: Calculate minute between two times interval with logical value

lifesafari — Mon, 19 Feb 2024 06:00:58 GMT

Hello, i resolve. I'm sorry for that