Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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.

 

ProjectDateStatus 
A1-SepCommitted 
A1-Octin Progress 
B1-SepCommitted 
B1-Octin Progress 
B1-NovTesting 
B1-DecDone 
    
 Results
Project3rd Quarter4th Quarter1st Quarter
AIn ProgressIn ProgressIn Progress
BCommitted 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 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous 

      Can you provide some sample data or picture?

       

      Best Regards!

      Yolo Zhu

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi,

        Something like this

         

        ProjectStatusQuarter 3Quarter 4Quarter 1Quarter 2
        ACommitted1   
        AIn Progress 111
        AAccepted    
        ADone    
        BCommitted1   
        BIn Progress    
        BAccepted    
        BDone 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!