topic Re: Subtract two date columns in existing table in DAX Commands and Tips

Subtract two date columns in existing table

sdhn — Wed, 17 Nov 2021 23:34:05 GMT

Hi All,

I am using Power Bi Desktop to develop report.

I need to add new column that computes elapsed time (Resolved date – Created date = Elapsed)

Both columns are date. Do I need to write mesaure ? etc

Please advise

Thanks

Re: Subtract two date columns in existing table

smpa01 — Thu, 18 Nov 2021 00:53:20 GMT

sdhn you can create a measure like this

difference = DATEDIFF(MAX('Table'[Created date]),MAX('Table'[Resolved date]),DAY)

or a calulated column

Column = CONVERT('Table'[Resolved date]-'Table'[Created date],DOUBLE)

whatever you prefer

Re: Subtract two date columns in existing table

VahidDM — Thu, 18 Nov 2021 07:14:38 GMT

Hi sdhn

Try this:

Elapsed = [Resolved date]-[Created date]

And change the new column data type to Whole number, Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Subtract two date columns in existing table

sdhn — Thu, 18 Nov 2021 15:38:28 GMT

Hi there,

thanks for your feedback. But I am not getting correct values.

Elapsed = CONVERT(MAX(Incident[Resolved Date Time])-MAX(Incident[Created]),DOUBLE)

Elapsed Diff = DATEDIFF(MAX(Incident[Created]),MAX(Incident[Resolved Date Time]),DAY)

Thanks

Re: Subtract two date columns in existing table

sdhn — Thu, 18 Nov 2021 15:47:04 GMT

Hi there ,

Thanks

This seems work. Only issue when we have no value in Resolved date time gives some numbers as:

Re: Subtract two date columns in existing table

smpa01 — Sat, 20 Nov 2021 03:17:25 GMT

sdhn did you try the above?

Re: Subtract two date columns in existing table

sdhn — Mon, 22 Nov 2021 15:08:19 GMT

Elapsed =

VAR _diff = [Resolved Date Time] - [Created]

RETURN IF([Resolved Date Time]=BLANK(),0,_diff)

Re: Subtract two date columns in existing table

NotMyJob — Wed, 24 Jan 2024 23:51:54 GMT

Yeah, I don't get that either with my table.

From:

Days Overdue = [Created Date] - [Due Date]

I get the error: The value for 'Created Date' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

Re: Subtract two date columns in existing table

NotMyJob — Wed, 24 Jan 2024 23:53:05 GMT

Hi @sdhn , see the comment about about the error I get with that equation: The value for 'Created Date' cannot be determined. Either the column doesn't exist, or there is no current row for this column.