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.

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.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi Richard77

       

      You can get duration in days using Power Query as well.

      Unfortunately the file does not allow me to edit, otherwise I would have done it for you.