cancel
Showing results for
Did you mean:
Frequent 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 ) )```

Arquivo PBIX

5 REPLIES 5
Frequent Visitor

Calculate working time between weekend and holiday

Good night,

I need to determine customer service time and for this, I must take into account the following items:

1. Eliminate national holidays

2. Eliminate the weekend from 12h on Saturday until 23:59:59 on Sunday.

Frequent Visitor

Help!

Highlighted
Frequent Visitor

Re: Calculate working time between weekend and holiday

```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?

Super User

Re: help with equation

See if this helps:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

Re: Calculate working time between weekend and holiday

Olá, este não exclui o sábado inteiro e não somente as horas.