cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bolabuga Member
Member

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

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

 

FORUM TABLES - MINUTOS EM DIAS UTEIS - BASE - 1.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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

View solution in original post

2 REPLIES 2
Super User
Super User

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

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

View solution in original post

bolabuga Member
Member

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

@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!!

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,771)