Current Date Minus Column Date if blank then Use another column

[Stuznet]

Hello, 

I'm struggling making this logic to work on power BI. The goal is to take

Date received <36, "0-35 Days", if is blank use Date Loaded<36, "0-35 Days"

Date received <46, "0-45Days", if is blank use Date Loaded<46, "0-45Days"

Date received <66, "0-65 Days", if is blank use Date Loaded<66, "0-65 Days"

If is less than 36/ 46/66, I need to display 0-35/ 0-45 Days and 0-66 days

This is the function I wrote, but I received an error indicating "Too few arguments were passed to the DATE function. The minimum argument count for the function is 3."

AgeBracket = SWITCH(
                TRUE(),
                'Date'[Date_Received] < DATE(TODAY(), "0-30 Days",
                'Date'[Date_Received] <= DATE(TODAY()
                    && ISBLANK('Date'[DateLoaded]),"0-30 Days",
                    'Date'[Date_Received] < DATE(TODAY(),"31-45 Days"
                        && ISBLANK('Date'[DateLoaded])=FALSE()
                        && 'Date'[DateLoaded] <= DATE(TODAY(),"31-45 Days",
                            "45+ Days"
                        

[Anonymous]

Hi @Stuznet

TODAY() itself returns current day, so you dont have to wrap TODAY with DATE function. So just get rid of DATE.

Ex: 'Date'[Date_Received] < DATE(TODAY(), "0-30 Days", --- Wrong one

     'Date'[Date_Received] < TODAY(), "0-30 Days", -- Correct one

Thanks

Raj

[Stuznet]

@Anonymous

I tried the correct one you pointed out but i'm getting this error

Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[Anonymous]

Hi @Stuznet

I just referred your message "Too few arguments were passed to the DATE function. The minimum argument count for the function is 3." and responded.

If i look at the full formula, it seems to be wrong. and also i dont get your logic : When you say , Date received <36 - Does that mean Date received - Today < 36, please explain.

Thanks
Raj

[Stuznet]

@Anonymous

Sorry about the trouble to decipher my message, I just don't know better to describe it. Yes,  Date received minus Today < 36 then output "0-35 Days" and If Date Received = blank look for Date Loaded then output "0-35 Days"

[Anonymous · Accepted solution]

Hi @Stuznet

No worries. Use the below formula.

Age Bucket =
 var InRec = SWITCH( TRUE(),VALUE(TODAY()-Table1[Date Received]) < 35, "0-35 days",
    VALUE(TODAY()-Table1[Date Received])< 45, "0-45 days",
    VALUE(TODAY()-Table1[Date Received])< 65, "0-65 days", "More than 65 days") 
    
var InLoad = SWITCH( TRUE(),VALUE(TODAY()-Table1[Date Loaded]) < 35, "0-35 days",
    VALUE(TODAY()-Table1[Date Loaded])< 45, "0-45 days",
    VALUE(TODAY()-Table1[Date Loaded])< 65, "0-65 days", "More than 65 days") 
    RETURN 
IF(ISBLANK(Table1[Date Received]), InLoad, InRec)

Sample result:

Thanks
Raj

[Stuznet]

@Anonymousthank you so much for your work! I modified the script to work with mine and it worked perfectly.

Age Bracket = 
 var DateReceived = SWITCH( TRUE(),VALUE(TODAY() -'Date'[Date_Received]) < 36, "0 - 35 Days",
    VALUE(TODAY() - 'Date'[Date_Received])< 46, "31 - 45 Days","45 + Days")
   
var DateLoaded = SWITCH( TRUE(),VALUE(TODAY() -'Date'[DateLoaded) < 36, "0-35 Days",
    VALUE(TODAY()-'Date'[DateLoaded)< 46, "31 - 45 Days" , "45 + Days")
    RETURN 
IF(ISBLANK('Date'[Date_Received]), DateLoaded , DateReceived)

[dedelman_clng]

For completeness sake, also note that you never properly closed the DATE function(s) in your original code

DATE( TODAY ()

vs

DATE( TODAY () )