Time to action Weekdays & Saturday (again)

[sovereignauto]

Good Afternoon,

I have already got some great advice on here and built a "time to action" DAX that worked perfectly for weekdays, im not trying to add the saturday in but i am missing somthing as the times are not correct.

I think i may need to add some sort of "if" statments on the partial day counting so it filters week days for the non "__SAT" so it only counts weekdays and the then the opposite but is there a more elegent way of doing it?

I have noticed it is counting a "minus" number as the time actioned was 4:30 pm but on the saturday we finish at 1pm (it didnt get actioned on a saturday) 

Time To Action Pending inc Sat = 



if (ISBLANK([Date Actioned Pending]) || ISBLANK([Date To Pending]) ,BLANK(),

    if (CLAIM[Date To Pending] > CLAIM[Date Actioned Pending] ,BLANK(),

  

VAR __startTime = TIMEVALUE("9:00 AM")    

VAR __endTime = TIMEVALUE("5:30 PM") 

VAR __SATstartTime = TIMEVALUE("9:00 AM")    

VAR __SATendTime = TIMEVALUE("1:00 PM")     

VAR __Start = CLAIM[Date To Pending]    

VAR __End = CLAIM[Date Actioned Pending]

VAR __WeekdayStart = WEEKDAY(__Start , 2)    

VAR __WeekdayEnd = WEEKDAY(__End , 2)    

VAR __NetWorkDays = COUNTX(            

    FILTER(                

        ADDCOLUMNS(                    

            CALENDAR(__Start,__End),                    

            "WeekDay",                    

            WEEKDAY([Date],2)                

            ),                

            [WeekDay]<6            

            ),            

            [Date]        

            )    

VAR __SATNetWorkDays = COUNTX(            

    FILTER(                

        ADDCOLUMNS(                    

            CALENDAR(__Start,__End),                    

            "WeekDay",                    

            WEEKDAY([Date],2)                

            ),                

            [WeekDay] = 6            

            ),            

            [Date]        

            ) 

VAR __fullDayMinutes = DATEDIFF(__startTime , __endTime , MINUTE) 

VAR __SATfullDayMinutes = DATEDIFF(__SATstartTime , __SATendTime , MINUTE) 



VAR __fullDays =         

SWITCH(TRUE(),            

    __WeekdayStart > 5 && __WeekdayEnd > 5 , __NetWorkDays,            

    __WeekdayStart > 5 || __WeekdayEnd > 5 , __NetWorkDays - 1,           

    __NetWorkDays < 2 , 0,            __NetWorkDays - 2        )    



VAR __SATfullDays =

SWITCH(TRUE(),

__WeekdayStart = 6 && __WeekdayEnd = 6 , __SATNetWorkDays - 2,

__WeekdayStart = 6 || __WeekdayEnd = 6 , __SATNetWorkDays - 1,

__SATNetWorkDays)



VAR __fullDaysDuration = __fullDays * __fullDayMinutes

VAR __SATfullDaysDuration = __SATfullDays * __SATfullDayMinutes    



VAR __startDayTime = TIME( HOUR(__Start) , MINUTE(__Start) , SECOND(__Start) )    

VAR __startDayDuration = DATEDIFF(__startDayTime , __endTime , MINUTE) 



VAR __SATstartDayTime = TIME( HOUR(__Start) , MINUTE(__Start) , SECOND(__Start) )    

VAR __SATstartDayDuration = DATEDIFF(__SATstartDayTime , __SATendTime , MINUTE) 



VAR __endDayTime = TIME( HOUR(__End) , MINUTE(__End) , SECOND(__End) )    

VAR __endDayDuration = DATEDIFF(__startTime , __endDayTime , MINUTE)

 

VAR __SATendDayTime = TIME( HOUR(__End) , MINUTE(__End) , SECOND(__End) )    

VAR __SATendDayDuration = DATEDIFF(__SATstartTime , __SATendDayTime , MINUTE)



VAR __TotalDays = __NetWorkDays + __SATNetWorkDays

 

RETURN    

    IF(        

        __TotalDays = 1,        

        DATEDIFF(__Start , __End , MINUTE),       

         __fullDaysDuration + __startDayDuration + __endDayDuration +  __SATfullDaysDuration + __SATstartDayDuration + __SATendDayDuration

          )))

  

[amitchandak]

@sovereignauto , Not very cleat, do you want to consider Saturday as workday ?

then you could have made the filter < 7

[sovereignauto]

@amitchandak yeah, but the hours are differnt on a saturday so i belive i need to treat it as a "seperate" calcualtion and then bring it together at the end. 

so count the Mon-Friday and then the Saturday then add it together 

[Greg_Deckler]

@sovereignauto Trying to understand what you are going for here. Do you have some sample data and expected output that you can post as text?

[sovereignauto · Accepted solution]

Thanks Greg, 

I acutaly found a way of getting it to work by setting the end time based off day of the week as a variable.