calculate business days between dates when the ID is the same and the number of step ascendent.

[PalmaJC]

Hello ,

I have the following table, i need to calculate the bussniess days between the created date and approved date, when the "wfrequestId" is the same, it must be taken into consideration that it is necessary to calculate between step numbers, for example, how much time passed between step 0 and step 1, between step 1 and step 2, between step 2 and step 3, between step 3 and step 4.

wfrequestId	Created date	module	numstep	approved date
475781	18/4/2022	HRIS	0	18/4/2022
475781	18/4/2022	HRIS	1	20/4/2022
475781	18/4/2022	HRIS	2	22/4/2022
475781	18/4/2022	HRIS	3	22/4/2022
475781	18/4/2022	HRIS	4	30/4/2022
78440	13/10/2020	GENERIC_OBJECT	0	6/12/2021
78440	13/10/2020	GENERIC_OBJECT	1	6/12/2021
78440	13/10/2020	GENERIC_OBJECT	2	19/12/2021

Thanks a lot for the support.

[tamerj1 · Accepted solution]

Hi @PalmaJC 
Here is a sample file with the solution https://www.dropbox.com/t/6wTvhg8rwEtOasqo

Saturday and Sunday are considered weekends.

Days Gap = 
VAR CurrentStep =
    Data[numstep]
VAR CurrentDate = 
    Data[approved date]
VAR CurrentIDTAble =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[wfrequestId] ) )
VAR PreviousStepTable =
    FILTER ( CurrentIDTAble, Data[numstep] = CurrentStep - 1 )
VAR PreviousDate = 
    MAXX ( PreviousStepTable, Data[approved date] )
RETURN
    IF (
        PreviousDate <> BLANK ( ),
        VAR DatesTable = 
            CALENDAR ( PreviousDate, CurrentDate )
        VAR DatesWithWeekday =
            ADDCOLUMNS ( DatesTable, "@Weekday", WEEKDAY ( [Date], 2 ) )
        VAR BusinessDates = 
            FILTER ( DatesWithWeekday, NOT ( [@Weekday] IN { 6, 7 } ) )
        RETURN
            COUNTROWS ( BusinessDates ) - 1
    )

[PalmaJC]

Thanks for the answer , only two observations, I need the bussiness days, in the ID = 475781 , between step 3 and 4 are 6 bussiness days, and second , the step 0 always be null or 0.

[Anonymous]

Hi @PalmaJC ,

Does the calculation consider the creation date? From your description it seems yes. I slightly modified the formula of tamerj1 to remove the -1 when it is returned, please check if it is what you want.

Days Gap 2 = 
VAR CurrentStep =
    Data[numstep]
VAR CurrentDate = 
    Data[approved date]
VAR CurrentIDTAble =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[wfrequestId] ) )
VAR PreviousStepTable =
    FILTER ( CurrentIDTAble, Data[numstep] = CurrentStep - 1 )
VAR PreviousDate = 
    MAXX ( PreviousStepTable, Data[approved date] )
RETURN
    IF (
        PreviousDate <> BLANK ( ),
        VAR DatesTable = 
            CALENDAR ( PreviousDate, CurrentDate )
        VAR DatesWithWeekday =
            ADDCOLUMNS ( DatesTable, "@Weekday", WEEKDAY ( [Date], 2 ) )
        VAR BusinessDates = 
            FILTER ( DatesWithWeekday, NOT ( [@Weekday] IN { 6, 7 } ) )
        RETURN
            COUNTROWS ( BusinessDates )
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[SunRiser]

Does this accomplish what you are looking for?

Dates_Between =

VAR _wfrequestid = 'Table'[wfrequestID]

VAR _numstep = 'Table'[numstep]

VAR _prev_numstep = _numstep - 1

VAR _approved_date = 'Table'[approved date]

VAR _create_date = 'Table'[Created]

VAR _prior_approved_date =

CALCULATE(

MIN('Table'[approved date]),

FILTER('Table', 'Table'[wfrequestID] = _wfrequestid && 'Table'[numstep] = _prev_numstep)

)

VAR _dates_between = IF(_numstep = 0, _approved_date - _create_date, _approved_date - _prior_approved_date)

RETURN _dates_between