Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

Function Related doesn't seem to work

Hey guys,   Im working on a formula that calculates the amount of hours between 2 dates based on working days. I've been wachting a clip on how to do that and i'm using this formula   Doorloopt...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

Here is a version for the calculated column that does not require the extra table. Start is the concatenation of StartDate and StartDateTime, End is the concatenation of EndDate and EndDateTime

 

 

 

 

BusHours Column = 
VAR b =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            FILTER (
                CROSSJOIN ( CALENDAR ( [StartDate], [EndDate] ), GENERATESERIES ( 0, 1439 ) ),
                WEEKDAY ( [Date], 2 ) < 7
                && [Value] >= 480
                && [Value] < IF(WEEKDAY ( [Date], 2 )=6,720,1200)
            ),
            "Min",
                [Date] * 1440 + [Value]
        ),
        "Value", [Min]
    )
RETURN
    COUNTROWS (
        INTERSECT (
            GENERATESERIES ( [Start] * 1440 ,  [End] * 1440  - 1 ),
            b
        )
    ) / 60