IF statement with custom dates

[Jackbaz99]

Hi all, my first post here so apologies if this is somewhere else but i cannot find ANYTHING to do with this.

Im trying to do a nested if statement with custom dates returning different values - the scenario is if the customer due date falls between for example 01/01/2019 and 01/09/2019 then the target is 0.75 if it falls between 02/09/2019 and 01/12/2019 then the target is 0.80 and so on so forth. im stuch below - dont think im too far away

I am getting a true/false data type with dates error.

Any help would be appreciated

Column = IF('Customer DSA'[Due Date].[Date] <= DATE(2019,1,1) >= DATE(2019,9,1),0,1)

[Greg_Deckler · Accepted solution]

@Jackbaz99 - So your formula should be:

Column = IF('Customer DSA'[Due Date].[Date] <= DATE(2019,1,1) && 'Customer DSA'[Due Date].[Date] >= DATE(2019,9,1),0,1)

That said, instead of nested IF statements, try using SWITCH(TRUE()...) instead. Much better than nested IF statements.

Since you are new, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[harshnathani · Accepted solution]

Hi @Jackbaz99 ,

Apart from the above mentioned solution, you can use SWITCH

Column =
SWITCH (
    TRUE (),
    'Customer DSA'[Due Date].[Date]
        <= DATE ( 2019, 1, 1 )
        && 'Customer DSA'[Due Date].[Date]
            >= DATE ( 2019, 9, 1 ), 0.75,
    'Customer DSA'[Due Date].[Date]
        <= DATE ( 2019, 9, 2 )
        && 'Customer DSA'[Due Date].[Date]
            >= DATE ( 2019, 12, 1 ), 0.80
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Jackbaz99]

@harshnathani Hi

Im not getting any return for this function. Just blanks.

[Jackbaz99]

@harshnathani @Greg_Deckler  Thank you both! The switch function is a godsend 😁 much appreciated!

[Greg_Deckler · Accepted solution]

@Jackbaz99 - So your formula should be:

Column = IF('Customer DSA'[Due Date].[Date] <= DATE(2019,1,1) && 'Customer DSA'[Due Date].[Date] >= DATE(2019,9,1),0,1)

That said, instead of nested IF statements, try using SWITCH(TRUE()...) instead. Much better than nested IF statements.

Since you are new, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.