topic Re: Date Difference of two column out of one column might having blank values in DAX Commands and Tips

Date Difference of two column out of one column might having blank values

Anonymous — Thu, 16 Feb 2023 13:58:31 GMT

Hello Everyone,

Your help will be appreciated for sure. I have two columns (CreatedDate and ResolvedDate) and I want to generate new column having difference of days of columns (CreatedDate and ResolvedDate). Some of the cells of ResolvedDate column are blank for that it should consider today's (Current) date.

The query is direct query which means it is live with database.

Re: Date Difference of two column out of one column might having blank values

tamerj1 — Thu, 16 Feb 2023 15:11:52 GMT

Anonymous

Since your data is live I would expect that the max date in either of the columns is today or worst case yesterday. Is that the case?

Re: Date Difference of two column out of one column might having blank values

Anonymous — Thu, 16 Feb 2023 15:27:51 GMT

tamerj1 Yes. Created date will be there in every cell which will be minimum but in resolved date (max date), some cells will be blank beacuse my some of cases are still active. So, in place of blank cell it should consider today's date (date on which person is seeing the power bi report) and in difference column (which I want to generate) there should be difference in days of both column (resolved date - created date)

Re: Date Difference of two column out of one column might having blank values

AilleryO — Thu, 16 Feb 2023 16:14:36 GMT

Hi,

Not sure about how your data are displayed but could it be something like :

Mesure =
VAR ResDate = IF ( ISNULL([ResolvedDate]) , today() , [ResolvedDate])
RETURN
DATEDIFF( [StartDate] , ResDate , xxx)

Hope it helps,

otherwise do not hesitate to provide sample datas without sensitive information...

Re: Date Difference of two column out of one column might having blank values

Anonymous — Fri, 17 Feb 2023 04:43:38 GMT

tamerj1 AilleryO

Hope this will make you clear, Please create query for this table. CreatedDate and ResolvedDate column is already there I have to calculate ResolutionTime column.

Re: Date Difference of two column out of one column might having blank values

tamerj1 — Fri, 17 Feb 2023 05:13:28 GMT

Anonymous
Let me explain further.
In normal cases it is just simple to return TODAY ( ) instead of a blank while creating a new calculated column. But using TODAY ( ) and NOW ( ) functions to create calculated columns is not allowed when the connection is Direct Query.

What I was thinking is if the maximum date value in either the CreatedDate or the ResolvedDate columns is actually the date of today, then we can easily retrieve that value and return it in case the ResolvedDate is blank.
So in normal cases I would use

NewCalculatedColumn = DATEDIFF ( Query1[StartDate], COALESCE ( Query1[ResolvedDate], TODAY () ), DAY )

but since TODAY ( ) won't work in a calculated column created with a direct query connection you may try

NewCalculatedColumn = DATEDIFF ( Query1[StartDate], COALESCE ( Query1[ResolvedDate], MAX ( Query1[ResolvedDate] ) ), DAY )

Re: Date Difference of two column out of one column might having blank values

Anonymous — Fri, 17 Feb 2023 05:37:49 GMT

tamerj1

Yes, I thought same that it will not work in case of Direct Query. However, I can replace Today's date in blank cell to string "Open"
So, if resolved date is blank then it should throw as "Open " in calculated column.

Please consider this one if it works for you Or else give me other suggestion for resolved date having blank cells.

Re: Date Difference of two column out of one column might having blank values

tamerj1 — Fri, 17 Feb 2023 06:01:47 GMT

Anonymous
Please try

NewCalculatedColumn = IF ( Query1[ResolvedDate] = BLANK (), "Open", DATEDIFF ( Query1[StartDate], Query1[ResolvedDate], DAY ) & " day(s)" )

Re: Date Difference of two column out of one column might having blank values

Anonymous — Fri, 17 Feb 2023 07:08:06 GMT

tamerj1

If I check the column "NewCalculatedColumn" then it is throwing error.

However, I had created custom column with below query in power query

and getting result but not as expected. For blank resolve cells, I want word as "Open" OR if it will consider today's date then I should get result in whole numbers as expected.

Re: Date Difference of two column out of one column might having blank values

tamerj1 — Fri, 17 Feb 2023 07:19:31 GMT

Anonymous
So you want to sum the days in a measure. In this case there in no need to create a calculated column, we can directly create a measure as follows

NewMeasure = COALESCE ( SUMX ( Query1, IF ( Query1[ResolvedDate] <> BLANK (), DATEDIFF ( Query1[StartDate], Query1[ResolvedDate], DAY ) ) ), "OPEN" )

Re: Date Difference of two column out of one column might having blank values

Anonymous — Fri, 17 Feb 2023 07:57:45 GMT

tamerj1

If I check this getting below error. I added query by selecting New Measure option.

Re: Date Difference of two column out of one column might having blank values

tamerj1 — Fri, 17 Feb 2023 08:08:21 GMT

Anonymous
I hate direct query. Please try

NewMeasure = COALESCE ( SUMX ( Query1, IF ( Query1[ResolvedDate] <> BLANK (), INT ( Query1[ResolvedDate] - Query1[StartDate] ) ) ), "OPEN" )

Re: Date Difference of two column out of one column might having blank values

Anonymous — Fri, 17 Feb 2023 08:47:05 GMT

tamerj1

Same result as previous.

Re: Date Difference of two column out of one column might having blank values

tamerj1 — Fri, 17 Feb 2023 09:12:31 GMT

Anonymous
I guess DateTime operations over a columns from a direct query connection are not allowed. That will bring us back to Power Query

= Table.AddColumn(Source, 'IMC Age", each if [ResolveDate]= "" then null else [ResolveDate]-[CreateDate])

NewMeasure = COALESCE ( SUM ( Query1[IMC Age] ), "OPEN" )

Re: Date Difference of two column out of one column might having blank values

Anonymous — Fri, 17 Feb 2023 14:56:50 GMT

tamerj1

Thank you for your valuable time. Unfortunately, none of query worked but I got result after changing it to KUSTO query directly.

Thank you so much for your quick responses.