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

[tusharkhodankar]

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.

[AilleryO]

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...

[tusharkhodankar]

@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.

[tamerj1]

@tusharkhodankar 

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?

[tusharkhodankar]

@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)

[tamerj1]

@tusharkhodankar 
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
)

[tusharkhodankar]

@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.

[tamerj1]

@tusharkhodankar 
Please try

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

[tusharkhodankar]

@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.

[tamerj1]

@tusharkhodankar 
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"
)

[tusharkhodankar]

@tamerj1 

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

[tamerj1]

@tusharkhodankar 
I hate direct query. Please try

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

[tusharkhodankar]

@tamerj1 

Same result as previous.

[tamerj1]

@tusharkhodankar 
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" )

[tusharkhodankar · Accepted solution]

@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.