Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Im searching for the correction of this formula
Column = IF(Project[StageName]="Bid Made" || Project[CloseDate]=< today();"Te laat"; "")
What im trying 2 do is to have a calculated column which checks that IF the stagename is 'Bid Made' and the CloseDate is 'smaller then or the same as today' it wil get the value 'Te Laat' en otherwise it does nothing.
What am i doing wrong ?
Solved! Go to Solution.
What I mean by no success is that the formula now returns 300 rows al with the value 'Te laat' and all have the stagename 'Bid Made' so that part works.
However if i check the closedate on these 300 rows the date doesn't match because there are rows in the result with the date of 25th of oktober and beyond.
So there is something wrong with the second part of the formula
Column = IF(
Project[StageName] = "Bid Made" &&
Project[CloseDate] <= TODAY();
"Te laat";
BLANK()
There's nothing wrong with that formula. Are you sure the date column you're checking is properly formatted as a date? If your system and that column disagree about date-month order it could be reading those dates incorrectly. Either that or those dates later than October 25th are in a different year and you didn't notice.
Proud to be a Super User!
Change this operator "=<" to " <=".
i changed the operator and it works
Check = IF(Project[StageName]="Bid Made" || Project[CloseDate]<= today();"Te laat"; "")
However i didn't get the result i wanted because when i check the outcome it almost always says 'te laat' however it also reads 'te laat' for the rows that doesn't have the StageName 'Bid Made', somhow the formula ignores the first part of the formula which states that the StageName should be Bid Made otherwise it shouldn't do anything
Im not sure where it goes wrong but the formula should check that IF the stagename is not 'Bid Made' it shouldn't do anything but if it does read 'Bid Made' and the Close Date is today or the past ( < = ) then it should read 'te laat'
Try this:
mycolumn = SWITCH(TRUE(),
OR(Project [StageName]="Bid Made", Project[CloseDate] <=TODAY()), "Te laat","")
Almost there i think.
My formule is this:
Column = SWITCH(TRUE();
OR(PROJECT[StageName]="Bid Made"; Project[CloseDate] <=TODAY(); "Te laat";"")
However the error states that 'The end of the imput was reached.'
@RvdHeijden you're missing a close parenthesis after TODAY().
Column = SWITCH(TRUE();
OR(PROJECT[StageName]="Bid Made"; Project[CloseDate] <=TODAY()); "Te laat";"")
Anyway there's no reason to use SWITCH if there's only one condition check. An IF statement would work fine and be easier to write.
Column = IF(
OR(PROJECT[StageName]="Bid Made"; Project[CloseDate] <=TODAY()); "Te laat";"")
Proud to be a Super User!
Both formulas give the same result and both aren't working properly.
The formula begins that IF the StageName is 'Bit Made' then it should check the close date. However this formule seems to ignore the first part of the formula.
I have the tekst 'Te laat' on rows with the status 'Order' or 'Order withdrawn' and that is strange seeing the formula should only do something IF the value is 'Bit made' so how come it works on other values ?
That's not the way you've written the formula. You used OR, meaning that if either one of the two conditions is met it evaluates as true. For what you're describing now you would have to use AND.
Proud to be a Super User!
Ive already tried to correct that but unfortunately with no succes.
Column = IF(AND(PROJECT[StageName]="Bid Made"; Project[CloseDate] <=TODAY()); "Te laat";"")
Am i forgetting something ?
No success in what way? What result are you getting now?
I really don't like the AND or OR functions anyway. It's easier to read if you use the operators && for AND, || for OR. Also I don't recommend using the Excel style "" in DAX in most cases. Use BLANK() instead, especially in a column where you want null values.
Column = IF( Project[StageName] = "Bid Made" && Project[CloseDate] <= TODAY(); "Te laat"; BLANK() )
Proud to be a Super User!
What I mean by no success is that the formula now returns 300 rows al with the value 'Te laat' and all have the stagename 'Bid Made' so that part works.
However if i check the closedate on these 300 rows the date doesn't match because there are rows in the result with the date of 25th of oktober and beyond.
So there is something wrong with the second part of the formula
Column = IF(
Project[StageName] = "Bid Made" &&
Project[CloseDate] <= TODAY();
"Te laat";
BLANK()
Hi @RvdHeijden,
The formula should work. Could you post your table structures and some sample data which can reproduce this issue in your case? It's better to upload a sample pbix file.
Regards
@KHorseman @v-ljerr-msft @Sean
Its kinda embarassing but you were right the 25th of october was in 2015......
I checked the dates and the formula works excellent, thank you all for the help
@RvdHeijden One thing to consider that would pass the condition in the formula is a blank CloseDate!
But you should not be getting any dates after today to pass (see picture)
Column = IF ( Project[StageName] = "Bid Made" && Project[CloseDate] <= TODAY (), "Yes", BLANK () ) Column 2 = SWITCH ( TRUE (), ISBLANK ( Project[CloseDate] ), BLANK (), Project[StageName] = "Bid Made" && Project[CloseDate] <= TODAY (), "Yes", BLANK () )
There's nothing wrong with that formula. Are you sure the date column you're checking is properly formatted as a date? If your system and that column disagree about date-month order it could be reading those dates incorrectly. Either that or those dates later than October 25th are in a different year and you didn't notice.
Proud to be a Super User!
User | Count |
---|---|
83 | |
77 | |
70 | |
69 | |
54 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |