Forum Discussion

Stuznet's avatar
Stuznet
Icon for Helper V rankHelper V
8 years ago
Solved

Current Date Minus Column Date if blank then Use another column

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", i...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Stuznet
Icon for Helper V rankHelper V
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Stuznet
      Icon for Helper V rankHelper V
      8 years ago

       

      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)