Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Month exclusion using IF statement

Hi,    I created a measure about the ranges amount of monthly GP with percent of deduction as a result.   Here is my code:   Group 1 = IF([Total GP] <= 325000 || [Total Collection Amt] <=...
Need Help
Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous if was not clear you are using measure

     

    Group 1 = IF(MAX('CALENDAR'[Date]) >= DATE(2020, 5, 1) &&  MAX('CALENDAR'[Date]) <= DATE(2020, 6, 30)), "No Deduction",
    IF([Total GP] <= 325000 || [Total Collection Amt] <= 1500000, "-25%", 
    IF([Total GP] > 325000 && [Total GP] <= 475000 || [Total Collection Amt] > 1500000 && [Total Collection Amt] <= 2500000, "-20%",
    IF([Total GP] > 475000 && [Total GP] <= 700000 || [Total Collection Amt] > 2500000 && [Total Collection Amt] <= 3500000, "-10%",
    IF([Total GP] > 700000 || [Total Collection Amt] > 3500000, "No Deduction", "WIP")))))
parry2k's avatar
parry2k
Super User
6 years ago

Anonymous I think you want this:

 

Group 1 = IF('CALENDAR'[Date] >= DATE(2020, 5, 1) &&  'CALENDAR'[Date] <= DATE(2020, 6, 30)), "No Deduction",
    IF([Total GP] <= 325000 || [Total Collection Amt] <= 1500000, "-25%", 
    IF([Total GP] > 325000 && [Total GP] <= 475000 || [Total Collection Amt] > 1500000 && [Total Collection Amt] <= 2500000, "-20%",
    IF([Total GP] > 475000 && [Total GP] <= 700000 || [Total Collection Amt] > 2500000 && [Total Collection Amt] <= 3500000, "-10%",
    IF([Total GP] > 700000 || [Total Collection Amt] > 3500000, "No Deduction", "WIP")))))

it is better to use switch function for easy readability and management.

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

shows me this error. It wont read

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous if was not clear you are using measure

     

    Group 1 = IF(MAX('CALENDAR'[Date]) >= DATE(2020, 5, 1) &&  MAX('CALENDAR'[Date]) <= DATE(2020, 6, 30)), "No Deduction",
    IF([Total GP] <= 325000 || [Total Collection Amt] <= 1500000, "-25%", 
    IF([Total GP] > 325000 && [Total GP] <= 475000 || [Total Collection Amt] > 1500000 && [Total Collection Amt] <= 2500000, "-20%",
    IF([Total GP] > 475000 && [Total GP] <= 700000 || [Total Collection Amt] > 2500000 && [Total Collection Amt] <= 3500000, "-10%",
    IF([Total GP] > 700000 || [Total Collection Amt] > 3500000, "No Deduction", "WIP")))))