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] <= 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"))))

 

I want to add one more filter, if the month is in May and June, i want it to show "No Deduction", but I'm having trouble with the code.

 

Here is my code that has problem:

 

Group 1 = IF(DATESBETWEEN('CALENDAR'[Date], DATE(2020, 5, 1), 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")))))

 

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

4 Replies

  • 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")))))
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , is it a measure or a column

    Try like

    'CALENDAR'[Date] >= DATE(2020, 5, 1) && 'CALENDAR'[Date]<= DATE(2020, 6, 30)