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.
Hi All,
I'am creating a new column in which I want to be able to determine if a project is active.
A project is active when:
Startdate is <= today and when the enddate is >= today. But sometimes the enddate is not filled.
If that is the case, the formula must look at a second value (End_Date_PO_Sub_agreement), when the second value is not filled the formula has to check a third value (Expected_Project_End_Date).
In some cases all the values are filled and then the priority must be the enddate.
Active projects = IF(Placement[cxsrec__Start_date__c] <= TODAY() && Placement[cxsrec__End_date__c] >= TODAY(); "active" ; IF(Placement[cxsrec__Start_date__c] <= TODAY() && Placement[Expected_Project_End_Date_TMC__c] >= TODAY(); "active" ; IF(Placement[cxsrec__Start_date__c] <= TODAY() && Placement[End_Date_PO_Sub_agreement__c] >= TODAY(); "active" ; BLANK())))
Any ideas would be much appericated. Thanks for any and all help in advance!
Solved! Go to Solution.
Sure, just flip the logic a little:
// Create a variable "end_date" that takes on the correct // value depending upon what columns are blank Active projects pseudo = VAR end_date = IF(ISBLANK(Placement[cxsrec__End_date__c]); //first if test IF(ISBLANK(Placement[End_Date_PO_Sub_agreement__c]); //second if test (true condition from first if) Placement[Expected_Project_End_Date_TMC__c]; //if PO End date is blank, use Project end date Placement[End_Date_Sub_agreement__c] //Otherwise, use the PO End Date ); //end second if test Placement[cxsrec__End_date__c] // false condition from first if, cxsrec__End_date__c is not blank ); //end first if test // Return is based upon the value of this variable "end_date" RETURN IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY(); "active"; //true condition BLANK() //false condition )
Psuedo code but the general idea should work:
Active projects = VAR end_date = IF(ISBLANK([cxsrec_End_date__c]),IF(ISBLANK([Expected_Project_End_Date_TMC__c]),[End_Date_PO_Sub_agreement__c,[Expected_Project_End_Date_TMC__c]),[cxsrec_End_date__c) RETURN IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY(), "active", BLANK())
Hi @Greg_Deckler,
Thank you very much for your quick response. Unfortunately the provide solution issn't working.
I tried to adapt the formula slightly but I receive a Syntax error for 'RETURN'.
Active projects pseudo = VAR cxsrec__End_date__c = IF(ISBLANK(Placement[cxsrec__End_date__c]);IF(ISBLANK(Placement[Expected_Project_End_Date_TMC__c]);(Placement[End_Date_PO_Sub_agreement__c]);(Placement[Expected_Project_End_Date_TMC__c]);Placement[cxsrec__End_date__c]); RETURN IF(Placement[cxsrec__Start_date__c] < TODAY() && Placement[cxsrec__End_date__c] >= TODAY(); "active"; BLANK()))
Greetings Ronald
You have a strange ";" at the end of the first line. I don't think that needs to be there.
Hi @Greg_Deckler,
I removed the strange ; at the end of the first line. But now I'am receive another error stating, 'Too many arguments were passed to the IF function. The maximum argument count for the function is 3.' Do you have another solution to solve this issue? Maybe the SWITCH function? But I don't no how to use is.
Greetings Ronald
I corrected a bunch of paren issues, etc. I think this makes it more clear what is going on.
// Create a variable "end_date" that takes on the correct
// value depending upon what columns are blank
Active projects pseudo =
VAR end_date =
IF(ISBLANK(Placement[cxsrec__End_date__c]); //first if test IF(ISBLANK(Placement[Expected_Project_End_Date_TMC__c]); //second if test (true condition from first if) Placement[End_Date_PO_Sub_agreement__c]; Placement[Expected_Project_End_Date_TMC__c]
); //end second if test Placement[cxsrec__End_date__c] // false condition from first if, cxsrec__End_date__c is not blank
); //end first if test
// Return is based upon the value of this variable "end_date" RETURN
IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY();
"active"; //true condition
BLANK() //false condition
)
@Greg_Deckler Hello Greg, and others,
I have a similar yet little different problem, I'd like to do the following:
Column 1 Column 2 Resulting Column
Row 1 Names 1 Names 2 Check if 'column 1' contains a list of names, if yes then return that name, else check 'column 2' if it contains the same list of names, if yes then return that name, else return "NA"
How do I go about this syntax where I have to check each cell if it contains multiple values referencing from a diferrent table( I can also add the namses since they're not that many) ?
Thanks in Advance
Hi @Greg_Deckler,
We are really close. Thank you very much for helping out.
The only thing we are missing is that the PO end date is more important than the expected end date. So as we see in this example the PO end date is later than the expected end-date, this project should be categorised as active.
Is there a way to take this into account?
Greetings Ronald
Sure, just flip the logic a little:
// Create a variable "end_date" that takes on the correct // value depending upon what columns are blank Active projects pseudo = VAR end_date = IF(ISBLANK(Placement[cxsrec__End_date__c]); //first if test IF(ISBLANK(Placement[End_Date_PO_Sub_agreement__c]); //second if test (true condition from first if) Placement[Expected_Project_End_Date_TMC__c]; //if PO End date is blank, use Project end date Placement[End_Date_Sub_agreement__c] //Otherwise, use the PO End Date ); //end second if test Placement[cxsrec__End_date__c] // false condition from first if, cxsrec__End_date__c is not blank ); //end first if test // Return is based upon the value of this variable "end_date" RETURN IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY(); "active"; //true condition BLANK() //false condition )
@Anonymous
I have not tested this (because I have no sample data)
But how about this...
Active projects = IF ( ISBLANK ( Placement[cxsrec__Start_date__c] ); "No Start Date"; IF ( Placement[cxsrec__Start_date__c] <= TODAY (); IF ( ISBLANK ( Placement[cxsrec__End_date__c] ); IF ( ISBLANK ( Placement[Expected_Project_End_Date_TMC__c] ); IF ( ISBLANK ( Placement[End_Date_PO_Sub_agreement__c] ); "No End Date of any kind"; IF ( Placement[End_Date_PO_Sub_agreement__c] >= TODAY (); "Active"; IF ( Placement[Expected_Project_End_Date_TMC__c] >= TODAY (); "Active"; IF ( Placement[cxsrec__End_date__c] >= TODAY (); "Active"; "Not Active" ) ) ) ) ) ) ) )
Good Luck!
EDIT: Obviously you can change the resulting text you don't really have to say "No End Date of any kind"
that's just for easier reading of the formula!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |