Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola mi duda es como hacer que se resten las horas en horario no laboral, aca un ejemplo:
Estoy restando las horas entre la fecha de envio y la fecha actual pero el primer resultado fue ingresado a las 01:00 y el otro a las 09:00 como puedo hacer que cuando este fuera del horario se resten las horas?
pd: las horas laborales son de entre las 09:00 hasta las 18:00
Solved! Go to Solution.
Cree una nueva columna:
Hours2 =
var start_date = 'Table (2)'[start]
var end_date = 'Table (2)'[end]
var Hours_List = SELECTCOLUMNS(GENERATESERIES((8), (16)), "hour", [Value])
var Minutes_List = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])
var Dates_List = CALENDAR(MIN('Table (2)'[start]),TODAY())
var HoursandMinutes = ADDCOLUMNS(
CROSSJOIN(Hours_List, Minutes_List),
"Time", TIME([Hour], [Minute], 00),
"Validity", IF([Hour]<9 || [Hour]>18,"Non working","Working")
)
var DateTimeTable = ADDCOLUMNS(CROSSJOIN(HoursandMinutes,Dates_List),
"weekday", WEEKDAY([date],2),
"New combined date",CONVERT(CONCATENATE([date],CONCATENATE(" ",[Time])),DATETIME)
)
var minutesfromstart = COUNTROWS(FILTER(DateTimeTable,start_date>=[New combined date] && [Validity]="Working" && [weekday]<6))
var minutesfromend = COUNTROWS(FILTER(DateTimeTable,end_date>=[New combined date] && [Validity]="Working" && [Weekday]<6))
var minutes = minutesfromend-minutesfromstart
var hours = DIVIDE(minutes,60)
return
IF(start_date=BLANK() || start_date>end_date || end_date=BLANK(),BLANK(),hours)
Cree una nueva columna:
Hours2 =
var start_date = 'Table (2)'[start]
var end_date = 'Table (2)'[end]
var Hours_List = SELECTCOLUMNS(GENERATESERIES((8), (16)), "hour", [Value])
var Minutes_List = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])
var Dates_List = CALENDAR(MIN('Table (2)'[start]),TODAY())
var HoursandMinutes = ADDCOLUMNS(
CROSSJOIN(Hours_List, Minutes_List),
"Time", TIME([Hour], [Minute], 00),
"Validity", IF([Hour]<9 || [Hour]>18,"Non working","Working")
)
var DateTimeTable = ADDCOLUMNS(CROSSJOIN(HoursandMinutes,Dates_List),
"weekday", WEEKDAY([date],2),
"New combined date",CONVERT(CONCATENATE([date],CONCATENATE(" ",[Time])),DATETIME)
)
var minutesfromstart = COUNTROWS(FILTER(DateTimeTable,start_date>=[New combined date] && [Validity]="Working" && [weekday]<6))
var minutesfromend = COUNTROWS(FILTER(DateTimeTable,end_date>=[New combined date] && [Validity]="Working" && [Weekday]<6))
var minutes = minutesfromend-minutesfromstart
var hours = DIVIDE(minutes,60)
return
IF(start_date=BLANK() || start_date>end_date || end_date=BLANK(),BLANK(),hours)
Buen dia!!
Disculpen como podria agregar a este mismo script los dias festivos?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
2 | |
1 | |
1 |