Exclude Null Data from Subtraction

[colem246]

Hello,

Im subtracting two columns that both include the date. Some of the cells are empty because no dates are available for the information.. How can I exclude the null information in the measure?

What I have so far is:

FirstTime = 'Activity '[First Time]-'Activity [Activity Date]

The result I get for the null values is just "12:00:00AM" but I would rather not have the data show.

Thanks

[Interkoubess]

Hi @colem246,

Please try something with an if like this:

if(isblank( or('Activity '[First Time]), 'Activity [Activity Date])),blank(), Activity '[First Time])-'Activity [Activity Date])

Let us know if it does not work...

Ninter

[colem246]

This is still giving me 12:00am even when the 'Activity '[First Time] is blank.

[Interkoubess]

hi @colem246,

Please give a sample data with your expected result and then I can try another approach.

Ninter

[colem246]

Here is an example of what it looks like. The last two columns are using the code you gave me. As you can see the columns at the top have missing data and still gives 12:00am in the last two columns. I would rather the last two columns be null.

Thanks

[Interkoubess]

Hi @colem246,

Could you change the postion of the Or condition like( replace the fields with the correct names):

Or(isblank(), isblanck())

I will make a try when I get a computer with Powerbi desktop.

But let me know.

Ninter

[Interkoubess]

Hi @colem246,

I tried the second approach proposed and it worked! 

Please try it and let me know if you have any issues.

IF(OR(ISBLANK('Activity First and Last'[First Arrival]),ISBLANK('Activity First and Last'[Activity Date])),BLANK(),'Activity First and Last'[Activity Date]-'Activity First and Last'[First Arrival])

Ninter

[colem246]

Hi Ninter,

I am still getting an error.

[Interkoubess]

Hi @colem246,

Are you creating this calculation as a column and not a measure?

 I created as a calculated column. Let me know if it is the case.

Ninter

[colem246]

I am using measure for this because I am ultimately going to be calculating the average time of the first, last, etc.....

Using a calculated column wouldnt allow for this type manipulation will it? Also, I only want the time stamp to show, not date and time since date is already populated in the activity date column.

Thanks

[Interkoubess]

Then use the formula with or with a combination of Datediff.

It should work...

Ninter.

[v-huizhn-msft]

Hi @colem246,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia