Forum Discussion

adriano321souza's avatar
adriano321souza
Frequent Visitor
7 years ago

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

      • adriano321souza's avatar
        adriano321souza
        Frequent 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?