cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anna_O
Frequent Visitor

SWITCH does not add the third value

Hello all,

 

I have a table of Course dates with columns Order Date, Sales Amount, etc. for purchasing other products before or after a course. I calculated a Date Difference between [Course Date] and [Order Date] in a separate column using DATEDIFF.

Now I want to attribute sales after the course to that course if the sale has happened not later than 90 days after the course, otherwise, I have different conditions.

I have this formula:

 

AfterSale = SWITCH(TRUE(),
AND([DateDiff]=0,[DateDiff]<=90),"After Course Sale",
AND([DateDiff]<0,[DateDiff]>=-90),"Before Course Sale",
[DateDiff]>-90,"Old Sales",
[DateDiff]>90,"Not Related")
 
I have two issues with this formula:
1. for some reason, Sales that happened between 0 and 90 days are attributed as "old Sales";
2. negative values are not attributed at all, I got blank cells.
 
Also, I can't include Value3. I don't know why, after Result2 in the formula I am only suggested to add Result3, but I want ti Have Value3 instead.
I want the formula to have 4 conditions (4 Values and 4 related Results).
 
What do I do wrong?
1 ACCEPTED SOLUTION

This is what I see. 
the first condition says "give me all results that are zero, and at the same time are less than 90".  This will return no result as there is no number that can be both. 
the second, third conditions have cross over. Eg -45 satisfies both 2 and 3. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2
Anna_O
Frequent Visitor

Of course! Thanks for pointing this to me, I need more sleep, haha.

This is what I see. 
the first condition says "give me all results that are zero, and at the same time are less than 90".  This will return no result as there is no number that can be both. 
the second, third conditions have cross over. Eg -45 satisfies both 2 and 3. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Top Solution Authors
Top Kudoed Authors