Forum Discussion

Richard77's avatar
Richard77
Resolver I
8 years ago
Solved

Project status duration

Hi all,   I have this logging table in which the status change of a certain project is registered. The table has an 'ID('unique), a 'projectID' a 'created_at'(date of change) and a string in which ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Richard77

     

    I worked on the file you sent. Please download from here

    I just replaced "VALUES" in the code with "MAX"

    Please check if the results are as per desire

    Actually there are some duplicate rows i.e. same( advisory_service_ID',old status and new status) as you can see in the Column "RowCount".
     Thats why VALUES didnot work.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi Richard77

 

Please check my solution here

Here is what I did

Created 2 calculated Columns "Old Status" and "New Status" based on the combinedstring in your data

Then created this measure

Duration Days =
VAR ChangedStatus =
    CALCULATE (
        VALUES ( Table1[created_at] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[projectID] ),
            Table1[Old Status] = VALUES ( Table1[New Status] )
        )
    )
VAR CurrentStatus =
    VALUES ( Table1[created_at] )
RETURN
    IF ( NOT ( ISBLANK ( ChangedStatus ) ), ChangedStatus - CurrentStatus )
  • Richard77's avatar
    Richard77
    Resolver I
    8 years ago

    Dear Zubair_Muhammad,

     

    thank you very much for your solution!

     

    I have tried it but i'm stuck at the following message:

    I have placed the file here: https://www.dropbox.com/s/xelkqpmrqk84obo/Status%20duration.pbix?dl=0

    if that would be of any help ;-)

    The 'projectID' is in the column 'advisory_service_ID' and i created 2 new columns for both the old and the new status by splitting the existing column until i have the correct number which represents the status in a single column.

     

    Perhaps i'm trying to grasp something here that is out of my reach.

     

    Cheers!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi Richard77

       

      I worked on the file you sent. Please download from here

      I just replaced "VALUES" in the code with "MAX"

      Please check if the results are as per desire

      Actually there are some duplicate rows i.e. same( advisory_service_ID',old status and new status) as you can see in the Column "RowCount".
       Thats why VALUES didnot work.

      • Richard77's avatar
        Richard77
        Resolver I
        8 years ago

        Thank you very much Zubair_Muhammad

        This is what i have been looking for.