Compare Date Time to NOW()

[mark_carlisle]

I am building a PowerBI report based off data in a SharePoint list.

I am looking to have a new column within the data to work out if the item is within SLA or not and gives the output Yes, No or Investigation.

There is a requirement to check if the list item is completed or not and use the correct calcualtion. If the list item is not complete it should check the SLA date time against NOW(). If it is complete then it will use 'SharePoint List'[Modified].

The expression below was working yesterday but now does not as the function DATEVALUE() removes the time element, converting that to 00:00.

WithinSLA = IF('SharePoint List'[Status]="Investigation", "Investigation", IF('SharePoint List'[Status]="Completed", IF(DATEVALUE('SharePoint List'[Modified])<DATEVALUE('SharePoint List'[SLA Date]), "Yes", "No"), IF(NOW()<DATEVALUE('SharePoint List'[SLA Date]), "Yes", "No")))

I am obviously missing something or is there another way to do what I want to do?

[Anonymous]

Hi @mark_carlisle,

Your formula seems well, have you double check you columns to confirm if they can convert to date by datevalue function?
In addtion, you can also try to use switch function with today fuction to check value:

WithinSLA =
SWITCH (
    'SharePoint List'[Status],
    "Investigation", "Investigation",
    "Completed", IF (
        DATEVALUE ( 'SharePoint List'[Modified] )
            < DATEVALUE ( 'SharePoint List'[SLA Date] ),
        "Yes",
        "No"
    ),
    IF ( TODAY () < DATEVALUE ( 'SharePoint List'[SLA Date] ), "Yes", "No" )
)

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[mark_carlisle · Accepted solution]

Within SLA = 
IF(
	'Data Services Dataset List'[Status]="Investigation", "Investigation", 
	IF(
		'Data Services Dataset List'[Status]="Completed", 
		IF(
			VALUE('Data Services Dataset List'[Modified]) <= VALUE('Data Services Dataset List'[SLA Date]), "Yes", "No"
		), 
		IF(
			VALUE(NOW()) <= VALUE('Data Services Dataset List'[SLA Date]), "Yes", "No"
		)
	)
)

Resolved with the above. Thanks for your help.