Duration between statuses

[Anonymous]

Good day All,

I have a bit of a conundrum. I have data as per below

I would like to calculate the duration that a specific "issue_id" has spent in a status (from_string and to_string). I would like the end user to be able to choose any "from_ string" and any "to_string" and then see the result as a sum value of all the statuses in between. What would the best way be to do this?

Edit: Typo

[Anonymous]

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

[Anonymous]

Thank you Liu Yang,

I tried something similar over the weekend. I created two seperate tables "To" and "From" in Power Query. I then link the two tables with one direction to the fact table and created measure where I am looking for the earliest From date and the Latest To date. It is working, but the calculation takes a while.

Your solution seems a bit more elegant and efficient. I am going to give it a try.

[AnkitKukreja]

Hi @Anonymous 

Not sure if I'm missing something, but I can't see "from_status" and "to_status". Also, can you share your sample file with expected solution so I can try to help with the solution.

[Anonymous]

Hi AnkitKukreja,

Sorry about the Typo, it should have been "from_string" and "to_string". I am not sure how to share files on here.

Below is a better example with possible outcomes I am looking for. The "Duration_Days" column I already managed to add in Power Query and calculates correctly. Whether this is the correct way or not I am not sure.