Not able to find the error in the formula (Time difference)..ANY good MAN AVAILABLE AT THIS TIME???

[christianfcbmx]

I have some DAX that Im using to count workhours between 08:00:00 and 18:00:00.... so, if Im considering dates and time like: 

07-03-2016 08:00:00

17-03-2016 18:00:00

In this example the difference of days are 10 but im not considering weekends so I have  90 hours so, if I change the start hour 07:00:00 I should get the same time difference (90 Hours) because of my restriction of counting, however I get 91 as you see in the pic... I realize that the problem is with the 08:00:00 restriction because I think is still counting when I add hours in the end  date anything change...after 18:00:00 is not counting so it is fine.

Any body could check what is not working in the formula???? I would be so happy really!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

The pbix and the excel is right there: https://www.dropbox.com/s/5b4zmo40k9dsiwl/time%20difference%20considering%20only%20friday%20to%20mo...

[christianfcbmx]

if I try to explain this differently:  is something like a SLA

For instant, I need to count hours between 2 different dates...for example I send a requirment to someone at 5:00:00 in the morning and he has 24 working hours to solve it...he starts working at 8:00:00 every day... the idea is start counting from 8:00:00 am and not from 5:00:00 am when I sent the requirment.

my problem is exactly that... I cant make my formula considers the counting from 8 am... in this case it starts counting at 5am when I sent it ,,, eventhough the other part is working pretty good because after 18:00 those formulas stop counting....but from midnight start counting again.

here is the pbix if is there is some nice people who can help me out: 

https://www.dropbox.com/sh/lrcbcgr577in687/AABOjff-YjZpuEJaGoL0e0wNa?dl=0

Thank you 

[christianfcbmx]

This is what happen and what I need:

Hopefully make sense to someone!

[christianfcbmx]

these are some cases with wrong output:

Here is the pbix

https://www.dropbox.com/s/5b4zmo40k9dsiwl/time%20difference%20considering%20only%20friday%20to%20mon...

[v-shex-msft · Accepted solution]

HI @christianfcbmx,

You can refer to below formula to get avaliabled workdays and remain work seconds.

Calculate columns:

Spoiler

Start = 
IF (
    HOUR ( [DateTimeFrom] ) <= 8,
    DATEVALUE ( [DateTimeFrom] ),
    DATEVALUE ( [DateTimeFrom] ) + 1
)
    + TIME ( 8, 0, 0 )


End = 
IF (
    HOUR ( [DateTimeTo] ) >= 18,
    DATEVALUE ( [DateTimeTo] ),
    DATEVALUE ( [DateTimeTo] ) - 1
)
    + TIME ( 18, 0, 0 )


Avaliabled WorkDays = 
IF (
    [Start] <= [End],
    COUNTROWS (
        FILTER (
            CALENDAR ( [Start], [End] ),
            NOT ( WEEKDAY ( [Date], 1 ) IN { 1, 7 } )
        )
    ),
    0
)


Remain Seconds = 
VAR _starttime =
    TIMEVALUE ( [DateTimeFrom] )
VAR _endtime =
    TIMEVALUE ( [DateTimeTo] )
VAR _8 =
    TIME ( 8, 0, 0 )
VAR _12 =
    TIME ( 12, 0, 0 )
VAR _18 =
    TIME ( 18, 0, 0 )
VAR remain_start =
    IF (
        _starttime >= _8
            && _starttime <= _18,
        IF (
            _starttime > _12,
            DATEDIFF ( DATEVALUE ( [DateTimeFrom] ) + _12, [DateTimeFrom], SECOND ),
            IF (
                _starttime <= _12,
                DATEDIFF ( [DateTimeFrom], DATEVALUE ( [DateTimeFrom] ) + _12, SECOND )
            )
        )
    )
VAR remain_end =
    IF (
        _endtime >= _8
            && _endtime <= _18,
        IF (
            _endtime > _12,
            DATEDIFF ( DATEVALUE ( [DateTimeTo] ) + _12, [DateTimeTo], SECOND ),
            IF (
                _endtime <= _12,
                DATEDIFF ( [DateTimeTo], DATEVALUE ( [DateTimeTo] ) + _12, SECOND )
            )
        )
    )
RETURN
    remain_start + remain_end
        + 0

Total Seconds = 
[Avaliabled WorkDays] * 10
    * 60
    * 60
    + [Remain Seconds]

Result:

Result

Notice:
1. Current my formula calculate 10 hours per workday.
2. I haven't consider the afternoon break time, if you have this requirement, please share more detailed info.

Regards,

Xiaoxin Sheng