Forum Discussion

SMITHY10283's avatar
SMITHY10283
Regular Visitor
2 years ago

Create calculated column

Hi, I need help in creating a calculated column and don't know where to start. 

 

So I have a dataset with Portfolio, Project, Reporting Date, Status as columns. 

 

PortfolioProjectReporting DateStatusTime to Completed
DataAAAA01 Apr 2022Started0
DataAAAA01 May 2022Completed1
DataAAAA01 June 2022On track0
DataAAAA01 July 2022Completed1
DataBBBB01 Apr 2022Completed0
DataBBBB01 May 2022Started0
DataBBBB01 June 2022Completed1
DataBBBB01 July 2022Completed

0

 

DivisonCCCC01 Apr 2022Completed0
DivisonCCCC01 May 2022Started0
DivisonCCCC01 June 2022Started0
DivisonCCCC01 July 2022Completed

2

 

 

What I want to calculate is the Time to Completed column (expected output in red), which is how long it took the status to turn to Completed. For example for the first project, it went from started to completed after 1 month in May and July, and for the second project it went Completed after 1 month in June, and similarly it took 2 months for the status to go to Green for the 3rd project. 

For the second project, since the month after (July) was also "Completed", the Time to Completed remains at 0. 
Also for the month in Apr in the same project the status is "Completed" but since that is the first entry that is recorded, there is no other Completed status to compare to hence why it is 0. 

How would I do this?

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    SMITHY10283 , Try meausre like , and find the file attcahed

     

    Progress1 = 
var _min = Maxx(filter(Projects, Projects[project] = earlier(Projects[Project]) && Projects[Status] = "Completed" && [Reporting Date] < earlier([Reporting Date])) , [Reporting Date])
var _min1 = if(ISBLANK(_min) , minx(filter(Projects, Projects[project] = earlier(Projects[Project])  && [Reporting Date] < earlier([Reporting Date])) , [Reporting Date]) , minx(filter(Projects, Projects[project] = earlier(Projects[Project])  && [Reporting Date] >_min && [Reporting Date] < earlier([Reporting Date])) , [Reporting Date]))
return
if(Projects[Status] = "Completed", DATEDIFF(_min1, [Reporting Date], MONTH),0
)