topic Re: IF statement with custom dates in DAX Commands and Tips

IF statement with custom dates

Jackbaz99 — Thu, 06 Aug 2020 12:17:12 GMT

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)

Re: IF statement with custom dates

Greg_Deckler — Thu, 06 Aug 2020 12:27:59 GMT

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.

Re: IF statement with custom dates

Anonymous — Thu, 06 Aug 2020 12:54:48 GMT

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)

Re: IF statement with custom dates

Jackbaz99 — Thu, 06 Aug 2020 13:05:13 GMT

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

Re: IF statement with custom dates

Jackbaz99 — Thu, 06 Aug 2020 13:51:27 GMT

Anonymous Hi

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