[ Useful Days time (in minutes) between 2 dates ]

[bolabuga]

Hello everyone.

I would like help in calculating the time between opening and closing date of a service order.

I need this time for "useful days" only, ignoring the time outside the useful day range.

I will post the link for the a sample file, that already have a date table with the classification for holidays, weekends and useful days.

sample file: https://drive.google.com/open?id=1CgF7c_v3DQD3OMCXGAr3DhEqr3BEda9E

[jdbuchanan71 · Accepted solution]

Hello @bolabuga 

Well, it's long but it should work for you.

TOTAL MINUTES IN USEFUL DAYS = 
VAR OpenDate = DATE ( YEAR ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), MONTH ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), DAY ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ) )
VAR CloseDate = DATE ( YEAR ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), MONTH ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), DAY ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ) )
VAR SingleDayMin =
    IF ( OpenDate = CloseDate,
        IF ( LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], OpenDate ) = 0, 0,
            DATEDIFF (
                'ORDERS DATE'[SERV. ORDER OPENING DATE],
                'ORDERS DATE'[SERV. ORDER CLOSING DATE],
                MINUTE ) ) )
VAR OpenDateMin =
    IF ( OpenDate = CloseDate || LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], OpenDate ) = 0, 0,
            DATEDIFF (
                TIME ( HOUR ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), MINUTE ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), 0 ),
                TIME ( 0, 0, 0 ) + 1,
                MINUTE ) )
VAR CloseDateMin =
    IF ( OpenDate = CloseDate || LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], CloseDate ) = 0, 0,
            DATEDIFF (
                TIME ( 0, 0, 0 ),
                TIME ( HOUR ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), MINUTE ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), 0 ),
                MINUTE ) )
VAR MidDaysMin =
    CALCULATE (
        SUM ( 'DATE TABLE'[WORKDAYFLAG] ),
        FILTER ( 'DATE TABLE', 'DATE TABLE'[DATE] > OpenDate && 'DATE TABLE'[DATE] < CloseDate )
    ) * 24 * 60
RETURN
    OpenDateMin + CloseDateMin + MidDaysMin + SingleDayMin

[bolabuga]

@jdbuchanan71 

Thanks!!!

I did serveral tests and it is working.

Lets see how it will performe in my much larger data base :).

I have some other bussiness rules, like if the service was open and closed at a weekend / holiday, instead of "zero", i should use the total time needed to solve the client problem, but i think i can, from your code, solve the other "rules".

Thanks again!!