Forum Discussion

mina97's avatar
mina97
Helper III
2 years ago
Solved

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...
"Need Help"
@Conditional Formatting
@Dax help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    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's avatar
mina97
Helper III
2 years ago

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

FreemanZ's avatar
FreemanZ
Super User
2 years ago

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: