date difference with one simple condition

[mina97]

hey i have the following data 

order	sumitied date	last action date	status
216	12/10/2023	13/10/2023	sumitied
216	13/10/2023	13/10/2023	returned to client
216	20/10/2023	21/10/2023	resubmited
216	22/10/2023	30/10/2023	Approved
217	10/10/2023	10/10/2023	sumitied
217	10/10/2023	10/10/2023	Approved

i want to calculate the date difference for the order from the day the order got retuned to client to the day the order got resubmitted only 
please help and thank you 

[v-zhangti · Accepted solution]

Hi, @mina97 

You can try the following methods. Create a new date table.

Date = CALENDAR(MIN('Table'[sumitied date]),MAX('Table'[last action date]))

Measure1 = 
Var _day1=CALCULATE(MAX('Table'[last action date]),FILTER(ALL('Table'),[status]="returned to client"))
Var _day2=CALCULATE(MAX('Table'[last action date]),FILTER(ALL('Table'),[status]="resubmited"))
Return
CALCULATE(COUNT('Date'[Date]),FILTER(ALL('Date'),[Date]>=_day1&&[Date]<=_day2&&[Weekday]<>5&&[Weekday]<>6))

Measure2 = 
Var _day1=CALCULATE(MAX('Table'[last action date]),FILTER(ALL('Table'),[status]="returned to client"))
Var _day2=CALCULATE(MAX('Table'[last action date]),FILTER(ALL('Table'),[status]="resubmited"))
Return
DATEDIFF(_day1,_day2,DAY)

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mina97]

1-exclude saturdays and fridays
2-including saturdays and fridays
🙂

[FreemanZ]

hi @mina97 ,

not sure if i fully get you, you may try to plot a table visual with order column and two measures like:

WithoutFriSat = 
VAR _table = 
FILTER(
    data,
    data[order] = MAX(data[order])
)
VAR _day1 =
MAXX(
    FILTER(
        _table,
         data[status] = "returned to client"
    ),
    data[last action date]
)
VAR _day2 =
MAXX(
    FILTER(
        _table,
         data[status] = "resubmited"
    ),
    data[last action date]
)
VAR _days =
COUNTROWS(
    FILTER(
        CALENDAR(_day1, _day2),
        NOT WEEKDAY([date], 2) IN {5,6}
    )
)
RETURN 
IF( 
    ISBLANK(_day1)&&ISBLANK(_day2 ),
    BLANK(),
    _days
)

WithFriSat = 
VAR _table = 
FILTER(
    data,
    data[order] = MAX(data[order])
)
VAR _day1 =
MAXX(
    FILTER(
        _table,
         data[status] = "returned to client"
    ),
    data[last action date]
)
VAR _day2 =
MAXX(
    FILTER(
        _table,
         data[status] = "resubmited"
    ),
    data[last action date]
)
RETURN INT(_day2 - _day1) 

it worked like: