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

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.