Duration between statuses

Hi  Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

From_Table =
DISTINCT('Table'[from_string])

To_Table =
DISTINCT('Table'[to_string])

2. Create measure.

from_string_measure =
var _from=SELECTEDVALUE('From_Table'[from_string])
var _to=SELECTEDVALUE('To_Table'[to_string])
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[isuue_id]=MAX('Table'[isuue_id])),"1",[from_string])
return
IF(
    _from in _column ,_from,"N/A"
)

to_string_measure =
var _from=SELECTEDVALUE('From_Table'[from_string])
var _to=SELECTEDVALUE('To_Table'[to_string])
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[isuue_id]=MAX('Table'[isuue_id])),"1",[to_string])
return
IF(
    _to in _column ,_to,"N/A"
)

Measure =
var _from=SELECTEDVALUE('From_Table'[from_string])
var _to=SELECTEDVALUE('To_Table'[to_string])
var _column1=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[isuue_id]=MAX('Table'[isuue_id])),"1",[from_string])
var _column2=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[isuue_id]=MAX('Table'[isuue_id])),"1",[to_string])
var _mindatefrom=
Minx(FILTER(ALL('Table'),'Table'[from_string]=_from),[Create])
var _maxdateto=
MAXX(FILTER(ALL('Table'),'Table'[to_string]=_to),[Create])
return
IF(_from in _column1 && _to in _column2,
SUMX(FILTER(ALL('Table'),
    'Table'[Create]>=_mindatefrom&&'Table'[Create]<=_maxdateto),[Duration_Days]
    ),
    "N/A"
)

3. Result:

From_string – Idea Logged

To_string – Rejected

From_string – Idea Logged

To_string – In IT Backlog

Best Regards,

Liu Yang

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