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!

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

### I have book! Learn Power BI from Packt

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.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 354 members 3,500 guests
Recent signins: