DATEDIFF Calculation with Condition

[Anonymous]

Hi guys, I need a bit of help; please see the below example.

I need Project Duration (Age) Calculation based on the below condition

* If Project status is ''In Progress'''  or ''blank'' Calculate the difference between the ''Start Date'' and ''Today''

* If project status is ''Completed'', then calculate the difference between the "Start date'' and ''Construction date''

Project Name	Start Date	Construction  date	Status	Project duration
A	1/10/2020	6/8/2021	In Progress
B	5/6/2019	7/5/2020	Completed
C	7/5/2021		In Progress
D	8/5/2020	11/11/2021	In Progress
E	6/4/2021		In Progress

[mahoneypat]

You can try a DAX calculated column with this expression

Duration =
IF(
    Table[Status] = "Completed",
    INT( Table[Construction Date] - Table[Start Date] ),
    INT( TODAY() - Table[Start Date] )
)

Pat

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Duration = 1*(if(Data[Status]="Completed",[Contruction date]-[Start date],today()-[Start date]))

Hope this helps.

[Anonymous]

Sorry mate its not working

[Ashish_Mathur]

What does not working mean?  Show a screenshot.  Share the download link of your PBI file.

[Anonymous]

Sorry My Bad. All good now thanks

[Ashish_Mathur]

You are welcome.