Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |