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 both the old and the new status are gegistered.

So, this logging would look like this:

  1. Project1, 21-01-2017,statusAtoStatusB
  2. Project1, 22-03-2017,statusBtoStatusC
  3. Project2, 24-03-2017,StatusAtoStatusB
  4. Project1, 25-04-2017,statusCtoStatusD
  5. Project2, 29-08-2017,status BtoStatusC

What i would like to create is an overview of which statuses a project has had and for how long this status was applicable for the project. What i cannot manage to do is the calculation for each status.

 

Is there anyone who can help me with this?

 

Cheers!

  • 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.

5 Replies

  • 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.