Forum Discussion
date difference with one simple condition
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
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.
3 Replies
1-exclude saturdays and fridays
2-including saturdays and fridays
🙂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:
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.
