Date of status shift

Hello,

I am needing some help building a measure (I think…) around when a project status changed.

Please see the attached snip for some dummy data. Project%20Records

Basically, I have a series of historical project records. I need to be able to report when a project status changed from one status to the next. For example, Project A went from “Initiate” to “Plan” on Jan 5. Some other attribute changed on January 6, causing the introduction of a new record. But the shift from “Initiate” to “Plan” happened on Jan 5.

For project D, the shift from “plan” to “execute” happened on January 15.

So, how can I build a query or measure that returns the date these shifts happen, without it getting confused by any subsequent records that repeat the same status?

Thanks for any help!

It’s important in this case to understand how you want to showcase this.

What visual or functionality are you looking for on you report?

Depending on what you’re looking to do with this will be a huge difference in complexity, so it’s important to understand this way more.

Chrs

Thanks for the reply. I want to be able to answer questions such as "In March, how many projects transitioned to status “Plan”? Right now I have these simply displayed on cards, but could see a desire to have these in line charts over time or something similar .

I developed a solution that seems to work, but am all ears if there is a more efficient way to do it. I developed a calculated column that does the following:

Status Effective Date = CALCULATE(min('table'[Start]),
    filter('Table',
        'Table'[Status] = EARLIER('Table'[Status])),
        'Table'[Name] = EARLIER('Table'[Name]))

This returns the earliest date that status exists for that Project. Is there a better way to accomplish this?

1 Like

This is a tough one. I’m glad you’ve found a solution. From what I can understand now that looks like a solid solution and I can’t think of a better one.

I’m presuming the calculated column is recording if there was a change and then you just counting up how many changes there are for each month.

Also which is your active relationship to the date table. I presume you have this some you can showcase by month? Are you more interested in the start or end date when filtering by dates?

Hi Sam,

Thanks for the affirmation! Yep, the calculated column returns the date of the change. I then have this setup for the key measure:

Plan = Calculate(counta('Table'[Status]), //count the number of records in the "Status" column
    filter('Table','Table'[Status]="Plan"), //return records when status is "plan"
    filter('Table','Table'[Start]='Table'[Start])) //return records when status effective date is equal to record start date

I think just pulled in a Slicer with “Start” as the slicer value, allowing me to set whatever time range I want to view changes in.

I’m more interested in the start date as that is the date the change occurs. As you can see from the example, the end date for the current record basically goes on forever.

This then allows for the following visualization:

Ok nice one. Looks good.