cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adriano321souza Frequent Visitor
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
adriano321souza Frequent Visitor
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.

adriano321souza Frequent Visitor
Frequent Visitor

Re: Calculate working time between weekend and holiday

Help!

adriano321souza Frequent Visitor
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?

Highlighted
Super User
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!


adriano321souza Frequent Visitor
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.