CODEWITHSUNDEEP
daxpowerbi-desktop
Adi Petrescu
Adi Petrescu

Reputation: 121

How to display custom values between date intervals using Dax in Power BI

I am trying to display custom values between different dates using the following formula in DAX (power Bi)

= if ([RegistrationDate] >= "6/14/2018" & [RegistrationDate] <= "7/15/2018") then "M4" else if ([RegistrationDate] >= "7/16/2018" & [RegistrationDate] <= "8/26/2018") then "M5" else if [RegistrationDate] >= "8/27/2018" then "M6" else ""

Basically the logic behind it is:

Using the above formula gives me an error. Any ideas why?

Upvotes: 1

Views: 2581

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

You need to use double ampersands && for logical AND in DAX and you don't use the words if, then, else if like you would in the M query editor language.

Marco's approach looks fine, but if you have a lot of else if cases, a SWITCH is probably better than nested IF functions.

Custom Column =
SWITCH ( TRUE(),
    'table'[RegistrationDate] >= DATE ( 2018, 6, 14 ) &&
    'table'[RegistrationDate] < DATE ( 2018, 7, 15 ),
    "M4",
    'table'[RegistrationDate] >= DATE ( 2018, 7, 16 ) &&
    'table'[RegistrationDate] <= DATE ( 2018, 8, 26 ),
    "M5",
    'table'[RegistrationDate] >= DATE ( 2018, 8, 27 ),
    "M6",
    "" 
)

Here's a reference on SWITCH TRUE.

Upvotes: 4

Marco Vos
Marco Vos

Reputation: 2968

What you want looks something like this:

Custom Column =
IF (
    'table'[RegistrationDate] >= DATE ( 2018, 6, 14 )
        && 'table'[RegistrationDate] < DATE ( 2018, 7, 15 ),
    "M4",
    IF (
        'table'[RegistrationDate] >= DATE ( 2018, 7, 16 )
            && 'table'[RegistrationDate] <= DATE ( 2018, 8, 26 ),
        "M5",
        IF ( 'table'[RegistrationDate] >= DATE ( 2018, 8, 27 ), "M6", "" )
    )
)

Upvotes: 0

Related Questions