Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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
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
@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.
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
@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"
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
@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)
For completeness sake, also note that you never properly closed the DATE function(s) in your original code
DATE( TODAY ()
vs
DATE( TODAY () )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |