Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RvdHeijden
Post Prodigy
Post Prodigy

Help Plz

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 ?

2 ACCEPTED SOLUTIONS

@KHorseman

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()

View solution in original post

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

14 REPLIES 14
BhaveshPatel
Community Champion
Community Champion

Change this operator "=<" to " <=". 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@BhaveshPatel

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","")

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@BhaveshPatel

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";"")





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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()
)

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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.Smiley Happy

 

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

Sean
Community Champion
Community Champion

@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 ()
)

 

2016-10-24 - SWITCH Statement.png

 

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.