Forum Discussion
adriano321souza
7 years agoFrequent Visitor
help with equation
Good afternoon,
I would like to adjust the equation below to the following case.
1. Calculate the start and end time of a process excluding holidays and weekend.
2. The weekend starts at 12AM on Saturday and runs until 00PM Sunday
3. I have the holidays column inside the calendar and the weekdays column
Minutes_Elapsed: =
VAR startDatetime = 'Fact'[Data_Hora_Abert]
VAR endDatetime =
IF (
ISBLANK ( 'Fact'[Data_Hora_Fech] ) || 'Fact'[Data_Hora_Fech] < 'Fact'[Data_Hora_Abert], // WARNING: fix this to address blanks
startDatetime,
'Fact'[Data_Hora_Fech]
)
VAR NormalRange = DATEDIFF( endDatetime, startDatetime, MINUTE )
VAR FilteredRange =
FILTER (
GENERATESERIES ( startDatetime, endDatetime, TIME ( 0, 1, 0 ) ),
NOT WEEKDAY ( [Value] ) IN { 1 } // Sunday
&& NOT ( WEEKDAY ( [Value] ) IN { 7 } && HOUR ( [Value] ) >= 0 && HOUR ( [Value] ) < 12 ) // Saturday
&& NOT ( MONTH ( [Value] ) = 12 && DAY ( [Value] ) = 25 ) // Christmas example
)
RETURN
NormalRange - (NormalRange - COUNTROWS ( FilteredRange ) )
5 Replies
- adriano321souzaFrequent Visitor
Good night,
I need to determine customer service time and for this, I must take into account the following items:
Eliminate national holidays
Eliminate the weekend from 12h on Saturday until 23:59:59 on Sunday.
Follow the file
- adriano321souzaFrequent Visitor
Help!
- adriano321souzaFrequent Visitor
Minutes_Elapsed: = VAR startDatetime = 'Fact'[Data_Hora_Abert] VAR endDatetime = IF ( ISBLANK ( 'Fact'[Data_Hora_Fech] ) || 'Fact'[Data_Hora_Fech] < 'Fact'[Data_Hora_Abert], // WARNING: fix this to address blanks startDatetime, 'Fact'[Data_Hora_Fech] ) VAR NormalRange = DATEDIFF( endDatetime, startDatetime, MINUTE ) VAR FilteredRange = FILTER ( GENERATESERIES ( startDatetime, endDatetime, TIME ( 0, 1, 0 ) ), NOT WEEKDAY ( [Value] ) IN { 1 } // Sunday && NOT ( WEEKDAY ( [Value] ) IN { 7 } && HOUR ( [Value] ) >= 0 && HOUR ( [Value] ) < 12 ) // Saturday && NOT ( MONTH ( [Value] ) = 12 && DAY ( [Value] ) = 25 ) // Christmas example ) RETURN NormalRange - (NormalRange - COUNTROWS ( FilteredRange ) )A friend gave me this code, but it is giving error, can someone help me?
- Greg_DecklerCommunity Champion