Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Duration between statuses

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

 

Need Help
Tips and Tricks

4 Replies

  • AnkitKukreja's avatar
    AnkitKukreja
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.