IF Statement with Multiple conditions in same IF Statement

[paulfink]

Hi guys,

ran into a problem.

im making a chart that shows if Completion date is between certain dates:

• If Comp Date < Date + 9 Months = "Within 9 Months"
• If Comp Date >= Date + 9 Months = "9 - 18 Months"
• If Comp Date > Date + 18 Months = "18 Months+"

I have done this by making a blank query then adding these formulas:

Source = DateTime.Date(DateTime.LocalNow()
Date.AddMonths([Today], 8)
Date.AddMonths([Today], 18)

I then made a formula for the conditions in the bullet points:

IF(Table1 [Completion Date] < RELATED('Date Revolve'[9 Month Revolve]),"Within 9 Months", BLANK()) & 
IF(Table1 [Completion Date] > RELATED('Date Revolve'[18 Month Revolve]), "18 Months +", BLANK())&
IF(Table1 [Completion Date] >= RELATED('Date Revolve'[18 Month Revolve]), "18 Months +", BLANK())

However, 9-18 and 18+ group in the same cells -"9-18 Months18Months +"

The conditions are overlapping.

How could i make it so that the 9-18 one has multiple conditions such as:

• If Comp Date >= Date + 9 Months and Comp Date < 18Months+ = "9 - 18 Months"

I tried using AND but then 9-18 disappears.

 = IF(AND(Table1[Completion Date] > RELATED('Date Revolve'[9 Month Revolve]),Table1[Completion Date] < RELATED('Date Revolve'[18 Month Revolve])), "9 - 18 Months", BLANK())

Any ideas?

[v-yingjl · Accepted solution]

Hi @paulfink ,

Not need to create relationships between two tables and use RELATED(), you can create a column like this:

Column =
IF (
    'Table'[Comp Date] < MIN ( 'Date'[9 Month Revolve] ),
    "Within 9 Months",
    IF (
        'Table'[Comp Date] > MIN ( 'Date'[9 Month Revolve] )
            && 'Table'[Comp Date] < MIN ( 'Date'[18 Month Revolve] ),
        "9 - 18 Months",
        "18 Months +"
    )
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11]

Hi @paulfink ,

You will have to modify your DAX and use the NESTED IF Statements:

IF
(
Table1[Completion Date] < RELATED('Date Revolve'[9 Month Revolve]),
 "Within 9 Months", 
  IF(Table1[Completion Date] >=  RELATED('Date Revolve'[9 Month Revolve]) && Table1[Completion 
     Date] <= RELATED('Date Revolve'[18 Month Revolve]), "9-18 Months", "18 Months +")
)

Thanks,

Pragati

[paulfink]

This got all 3 but 18 Months + is showing up on Completion dates that should be under Within 9 Months.

[Pragati11]

Hi @paulfink ,

Can you share some screenshot of the output you are getting please?

[paulfink]

As you can see, there are some 2018, 19, 20 and 21 data that should be going under Within 9 Months. I have tried altering your formula but have had no luck unfortunately.

[Pragati11]

Hi @paulfink 

How have you calculated the following columns:

1. DateRevolve[9 Month Revolve]
2. DateRevolve[18 Month Revolve]

Thanks,

Pragati

[paulfink]

No, i made them in Power Query. They are in a seperate query thats why I have to use the RELATED function

Source = DateTime.Date(DateTime.LocalNow()
Date.AddMonths([Today],8)
Date.AddMonths([Today], 18)

[Pragati11]

Hi @paulfink ,

I think what is wrong in your above calculation is, you need to add 9 months to the Completion Date column not TODAY's date.

Because in IF-ELSE you are comparing with COMPLETION DATE column.

Basically just create 2 columns in the same table where you have completion date column:

Date.AddMonths(Table1[Completion Date], 9)

Date.AddMonths(Table1[Completion Date], 18)

Then the DAX what I shared should work and will give you right classification.

IF
(
Table1[Completion Date] < 'Table1'[9 Month Revolve],
 "Within 9 Months", 
  IF(Table1[Completion Date] >=  'Table1'[9 Month Revolve] && Table1[Completion 
     Date] <= 'Table1'[18 Month Revolve], "9-18 Months", "18 Months +")
)

Thanks,

Pragati

[paulfink]

Sorry but thats not how we want to see the data. If we change the 9 and 18 month to base it on Completion date, then the figures would stay the same.

Basing it on Todays day would mean the figures would change and we can see what is coming up and what is in the far future.

[Pragati11]

Hi @paulfink ,

Ok in that case, can you attach some sample data here for me to try it at my end?

Thanks,

Pragati

[paulfink]

https://www.dropbox.com/s/42r9w9dhnnxmdek/Dummy%20Data.csv?dl=0

@Pragati11