Forum Discussion
DATEDIFF Calculation with Condition
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 |
|
Hi,
Write this calculated column formula
Duration = 1*(if(Data[Status]="Completed",[Contruction date]-[Start date],today()-[Start date]))
Hope this helps.
6 Replies
Hi,
Write this calculated column formula
Duration = 1*(if(Data[Status]="Completed",[Contruction date]-[Start date],today()-[Start date]))
Hope this helps.
What does not working mean? Show a screenshot. Share the download link of your PBI file.
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
