topic Re: date difference with one simple condition in DAX Commands and Tips

date difference with one simple condition

mina97 — Thu, 26 Oct 2023 07:59:49 GMT

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

Re: date difference with one simple condition

mina97 — Thu, 26 Oct 2023 08:05:49 GMT

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

Re: date difference with one simple condition

FreemanZ — Thu, 26 Oct 2023 08:41:09 GMT

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:

Re: date difference with one simple condition

v-zhangti — Mon, 30 Oct 2023 10:44:21 GMT

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.