How to write a DAX Formula in Power Pivot to subtract 2 cells with MM/DD/YYYY HR:MIN:SEC from Another

[jdlibra75]

Hello,
I am new to this forum and new to Power Pivot. I have been reviewing forums for a while now and cannot seem to find a formula that will do the following calculation (which I use in Excel 2016 and it works). It will subtract the CompletedDateTime from the CreateDateTime and tell me if the difference is less then 24 hours. Here is an example of the excel formula i currently use:

=IF([CompletedDateTime (UTC)]<>FALSE,IF([CompletedDateTime (UTC)]-[CreateDateTime (UTC)]<1,1,""),"")

Example of Table:
[TABLE="width: 491"]
<tbody>[TR]
[TD]
CreateDateTime (UTC)[/TD]
[TD][/TD]
[TD]CompletedDateTime (UTC)[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2016 12:08 AM
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/01/2016 01:46 AM[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2016 12:10 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/01/2016 01:25 AM[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2016 12:12 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/01/2016 12:27 AM[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2016 12:13 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/01/2016 12:43 AM
[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


I would like this to be all in one single formula and output to a single cell.
Please help me !!!

 

[jdlibra75]

More Data from Table. Also if less then 1 day it will display a 1, if not then leaves Blank.

[TABLE="width: 278"]
<tbody>[TR]
[TD]CreateDateTime[/TD]
[TD]CompletedDateTime[/TD]
[/TR]
[TR]
[TD]01/01/2016 12:53 AM[/TD]
[TD]01/04/2016 06:19 PM[/TD]
[/TR]
[TR]
[TD]01/01/2016 01:00 AM[/TD]
[TD]01/06/2016 08:20 PM[/TD]
[/TR]
[TR]
[TD]01/01/2016 01:02 AM[/TD]
[TD]01/01/2016 01:50 AM[/TD]
[/TR]
[TR]
[TD]01/01/2016 01:05 AM[/TD]
[TD]01/07/2016 04:02 AM[/TD]
[/TR]
[TR]
[TD]01/01/2016 01:12 AM[/TD]
[TD]01/01/2016 02:20 AM[/TD]
[/TR]
[TR]
[TD]01/01/2016 01:18 AM[/TD]
[TD]01/02/2016 11:25 PM[/TD]
[/TR]
[TR]
[TD]01/01/2016 01:19 AM[/TD]
[TD]01/01/2016 01:26 AM[/TD]
[/TR]
[TR]
[TD]01/01/2016 01:23 AM[/TD]
[TD]01/01/2016 01:38 AM[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Matt Allington]

Power pivot is not the same as Excel, and it is common for new users to try to do things in Power Pivot that they are used to doing in Excel. That may or may not be the best approach.

You don't mention if you are trying to write a calculated column here - are you? If so, my guess is there is a better approach, but I don't really know what you are trying to do.

Putting that aside, If this is a calc column, you can definatley write a custom column in this format.

=if(table[column1]-table[column2]<1,1)

you you don't need to add the alternate result. If you want to, it should be BLANK() and not "". When you use "" you are actually returning a text result, but the first part of the statement returns a number. You are not allowed to split the results into different data types like this in Power Pivot

Also you should always read the error messages as they normally tell you want the problem is, even if they are sometime a bit hard to understand.

Good luck