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 want to use an IF statement to create three conditions based on appointments for bookings.
I have three columns of times. Created Datetime, Failture datetime and Succeed Date Time.
The calculation must be relative and take account of the actual date of today.
The three conditions...
1. If succeed datetime is blank and the failure datetime is prior todays date (22/01/2020) then the condition is "Fail"
2. if the failture date is ahead of todays date and the succeed datetime is blank then condtion is "Inside SLA"
3. IF the succeed column has a date that is before the failture column then "Pass"
All of these work with the current function
SLA = IF(SLA[Faildate] < today() || SLA[Faildate] < SLA[Succeed], "Fail", IF(ISBLANK(SLA[Succeed]&&(SLA[Fail]>Today())), "Inside" SLA","Pass"))
The issue is that the equation doesnt take into account these Cases. These should be passes because the succeed date is eariler than the fail date.
Anyway around this?
Solved! Go to Solution.
Hi @Anonymous ,
I think the problem is in the first part of your if statement. Where you say; IF(SLA[Faildate] < today() || SLA[Faildate] < SLA[Succeed], "Fail". You use an 'or' statement here, so if the the faildate < today (which is the case for you last 4 lines) or faildate<succeed then the SLA status is Fail.
The Columns SLA2 is your IF statement. SLA3 is showing the expected result (if I am not mistaken)
SLA3 looks like :
SLA3 =
switch(
true(),
ISBLANK(SLA[Succeed]) && SLA[FailDate] <TODAY(),"Fail",
ISBLANK(SLA[Succeed]) && SLA[FailDate] >= TODAY(), "Inside SLA",
SLA[Succeed]<=SLA[FailDate], "Pass",
"")
I think it is easier to read and I hope it helps.
jan
if this is a solution for you, don't forget to mark it as such. thanks
Jan
Hi @Anonymous ,
I think you have an issue with the brackets on the bold part of the statement. Now you are testing if the combination of fields isblank, but you shoul only test if succeed is blank (at least that is what I thoug was the idea.
Jan
Hi JustJan,
Yes added the bracket in for this part
and it has worked.
Thank you
Hi @Anonymous ,
I think the problem is in the first part of your if statement. Where you say; IF(SLA[Faildate] < today() || SLA[Faildate] < SLA[Succeed], "Fail". You use an 'or' statement here, so if the the faildate < today (which is the case for you last 4 lines) or faildate<succeed then the SLA status is Fail.
The Columns SLA2 is your IF statement. SLA3 is showing the expected result (if I am not mistaken)
SLA3 looks like :
SLA3 =
switch(
true(),
ISBLANK(SLA[Succeed]) && SLA[FailDate] <TODAY(),"Fail",
ISBLANK(SLA[Succeed]) && SLA[FailDate] >= TODAY(), "Inside SLA",
SLA[Succeed]<=SLA[FailDate], "Pass",
"")
I think it is easier to read and I hope it helps.
jan
if this is a solution for you, don't forget to mark it as such. thanks
Jan
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |