Switch Formula with multiples condition based off existing measures and a column i have created.

[forgetmenot]

I am a newbie and am really struggling with getting the logic right to create a Switch Formula. 

I have been asked to provide the following: 

If Variance is within Qtr1 FY (April to Jun) then tolerance should be within +/-10%  or 500,000 whichever is less, or if it is in Qtr 2 FY (july-sep) then again i need to check whether its within +/-10%  or 500,000. 

My dataset only had a month field,  I created a calender table to work out FYQ, however im not sure how to reference this now in a switch formula to satisfy the above conditions and return either "in tolerance" or "outside tolerance".

Can someone please assist?


So far i have: 

Switch Q1 = SWITCH (

      TRUE (),

[MONTH_%_DIFF]>[T1_HIGH] ||[MONTH_%_DIFF]<[T2_LO],"Yes",

"No")

Im not sure how to add in a further condition to check what the financial quarter is and the appropriate tolerance i.e 250,000 or 500,000

Any help you provide woudl be much apprecaited. 

[Alef_Ricardo_ · Accepted solution]

To implement the logic you described with a switch formula based on financial quarters and tolerance levels, you can use the `SWITCH` function along with additional `IF` statements to check the financial quarter and apply the appropriate tolerance criteria. Here's a DAX formula that accomplishes this:

```DAX
Result =
VAR CurrentQuarter = SELECTEDVALUE(Calendar[FYQ])
VAR Variance = [MONTH_%_DIFF]
VAR ToleranceLevel =
SWITCH(
TRUE(),
CurrentQuarter = "Q1 FY" && (Variance > 0.1 || Variance < -0.1), 500000,
CurrentQuarter = "Q2 FY" && (Variance > 0.1 || Variance < -0.1), 500000,
TRUE(), 100000
)
VAR WithinTolerance =
IF(
ABS(Variance) <= ToleranceLevel,
"In Tolerance",
"Outside Tolerance"
)
RETURN
WithinTolerance
```

Here's how this formula works:

1. `CurrentQuarter` calculates the financial quarter based on your calendar table.

2. `Variance` represents the percentage difference that you have.

3. `ToleranceLevel` calculates the tolerance level based on the financial quarter and whether the variance exceeds 10%. If it's Q1 or Q2 and the variance exceeds 10%, the tolerance is 500,000. Otherwise, it's 100,000.

4. `WithinTolerance` checks whether the absolute value of the variance is within the calculated tolerance level and returns "In Tolerance" or "Outside Tolerance" accordingly.

5. The `Result` measure returns the result of the `WithinTolerance` calculation.

This formula considers the financial quarter, percentage variance, and appropriate tolerance level, and then returns "In Tolerance" or "Outside Tolerance" based on these conditions. Adjust the tolerance levels and conditions as needed to match your specific requirements.

[forgetmenot]

@Alef_Ricardo_ , Many thanks for your help and also apprecaite the thorough explanation. 

I have tried your dax and its working now!!! 🙂 Thank you 

[forgetmenot]

Sample data as canot provide original dataset. 

i have used dax to calculate % difference between months. Calender table is set up to give me info like what FQ i am in. 

Can someone please assist how to carry out check what the FQ is and apply relevant tolerance

Tolerance is  +/-10% diff from previous Q or 500,000 (for Q1 and Q2)
and % diff tolerance is  +/-10% diff from previous Q or 250,000 (For Q3 ad Q4)

[Dangar332]

hi,  @forgetmenot 
try below 

Switch Q1 = SWITCH (

      TRUE (),

[MONTH_%_DIFF]<=[T1_HIGH] ||[MONTH_%_DIFF]>=[T2_LO]||[MONTH_%_DIFF]<500000,"Yes",

"No")

Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

[forgetmenot]

Thank you for taking the time to respond. 

Apologies, It seems I have made a typo, it should have read that the tolerance should be +/-10% or 500,000 for Qtr1 or if its Qtr 2 then it should be +/-10% or 250,000.

so the checks that need to be done are first look at FY column (i added to dataset as a column so its not part of original dataset)

 

determine what Q it is,

if its Q1 then tolerance is +/-10% diff from previous Q or 500,000.
if its Q2 then tolerance is  +/-10% diff from previous Q or 250,000, i hope that makes sense.  so i need tio incorporate these additional criteria of dtermining the FYQ and applying 500,000 tolerance for Q1 or 250,000 tolerance f its Q2. 

[Dangar332]

hi, @forgetmenot  

provide sample data

[forgetmenot]