cancel
Showing results for
Did you mean:
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.
2 REPLIES 2
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.

Announcements