Expected Date calculated Column

[Bajdak]

Hello,

i have two columns. "Startdatetime" and "Enddatetime". The data is connected to SQL and is connected via DirectQuery. This data is constantly changing as SQL is related to a running system. Sometimes it can be, that the Enddatetime is "1900-01-01" because the task is running and the Enddatetime is not defined.  "Startdatetime" can be also "1900-01-01", when the task is not startet. I need a calculated Column in Power BI, that is adding the Average of the Duration between "Startdatetime" and "Enddatetime" from the others Data to "Startdatetime" to the data, where Enddatetime is not defined.

As example:

The task on "02/25/2023" took about 5 hours. In the 5 hours the Enddatetime was "1900-01-01". Only when the task was over was the record updated. I need a Calculated Column like IF Enddatetime =1900-01-01 then calculate Startdatime + Average of other records else Enddatetime. The point is that you can predict the task with the average running time of others.

[amitchandak]

@Bajdak ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[Bajdak]

I have updated the situation.