Project status duration

[Richard77]

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]

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]

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 · Accepted solution]

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]

Thank you very much @Zubair_Muhammad

This is what i have been looking for.

[Zubair_Muhammad]

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.