Project Count Over time that adds uncompleted projects to future time periods

[bcobrien1977]

Hi,

I am trying to track project progress across 4 stages, (Committed, In Progress, Testing and Done).

The tricky part is I want to take the max status each quarter and if it hasn't reached done status, to carry that to the next quarter.

In the example below, project A would carry forward to each quarter because it hasn't achieved done status.

The results would look like this.

Project	Date	Status
A	1-Sep	Committed
A	1-Oct	in Progress
B	1-Sep	Committed
B	1-Oct	in Progress
B	1-Nov	Testing
B	1-Dec	Done
	Results
Project	3rd Quarter	4th Quarter	1st Quarter
A	In Progress	In Progress	In Progress
B	Committed	Done

My measure looks like this but I can't seem to figure out how to factor in the Done status (essentially carrying the project to each successive quarter).

Flow Distribution Count =

VAR MAXSELECTDATE=max(vwFiscalCalendar[CalendarDate])

RETURN

SUMX('FLOW DISTRIBUTION_FEATURE_EPIC',

VAR createdate='FLOW DISTRIBUTION_FEATURE_EPIC'[CreatedOn]

VAR creatednextdate='FLOW DISTRIBUTION_FEATURE_EPIC'[CreatedOnNext]

VAR laststatus='FLOW DISTRIBUTION_FEATURE_EPIC'[NewString]

RETURN IF(createdate<=MAXSELECTDATE && or(creatednextdate>=MAXSELECTDATE,creatednextdate=Blank()),1,0))

I appreciate any help 

[bcobrien1977]

Thanks!.  What would happen if I wanted to count the projects that didn't reach done status?

In other words, just count projects by status by quarter, using the same logic.

Thanks

Brendan

[v-xinruzhu-msft]

Hi @bcobrien1977 

Can you provide some sample data or picture?

Best Regards!

Yolo Zhu

[bcobrien1977]

Hi,

Something like this

Project	Status	Quarter 3	Quarter 4	Quarter 1	Quarter 2
A	Committed	1
A	In Progress		1	1	1
A	Accepted
A	Done
B	Committed	1
B	In Progress
B	Accepted
B	Done		1
	Project would pick up max status in quarter and put it in that status.
	Anything not in Done Status would carry on indefinitely. Project A would remain in the last status until an update is made.  THanks!

[bcobrien1977]

Hi, Did you ever have a solution to this? It feels like there should be a way to add an uncompleted project to a project inventory.

Thanks

[v-xinruzhu-msft]

Hi @bcobrien1977 

You can refer to the following example

Sample data 

Then create a measure

Measure = var b=FILTER(ALL('Table'),[Project]=MAX([Project])&&[Status]="Done")
var c=MAXX(b,[Quarter])
var d=MAXX(FILTER(ALL('Table'),[Project]=MAX('Table'[Project])),[Date])
var e=MAXX(FILTER(ALL('Table'),[Quarter]=MAX([Quarter])),[Date])
return IF(COUNTROWS(b)>0,IF(MAX('Table'[Quarter])=c,"Done",MAXX(FILTER(ALL('Table'),[Date]=e),[Status])),MAXX(FILTER(ALL('Table'),[Project]=MAX('Table'[Project])&&[Date]=d),[Status]))

And put the measure to the matrix visual

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.