Forum Discussion

mark_carlisle's avatar
mark_carlisle
Icon for Advocate IV rankAdvocate IV
8 years ago
Solved

Compare Date Time to NOW()

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?

  • mark_carlisle's avatar
    mark_carlisle
    8 years ago
    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      mark_carlisle
      Icon for Advocate IV rankAdvocate IV
      8 years ago
      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.