Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello fellow at-home warriors,
I am attempting to build a date different measure to account for a range of bad dates data that spans a couple decades in total. I know which line is creating the issue, and it's telling me it has something to do with the data type (format) of the logic function. I've attempted using the following conversions to correct but get errors, usually the same:
Here is my code:
Test Begin Date =
var YrTo = YEAR( tbl_Orders_DatesDays[BILLTO_DATE] )
var YrFrom = YEAR( tbl_Orders_DatesDays[BILLFROM_DATE] )
var YrToday = YEAR( TODAY() ) + 1
var YrRet = YEAR( tbl_Orders_DatesDays[RETURN_DATE] )
var YrCreated = YEAR( tbl_Orders_DatesDays[CREATED_DATE] )
VAR YrShp = YEAR( tbl_Orders_DatesDays[SHIP_DATE] )
RETURN
DATE(
SWITCH( TRUE() ,
ABS( FORMAT( ( ( YrFrom - ( YrCreated - 5 ) ) ) , "General Number" ) ) > 5 , YrShp ,
AND ( YrFrom < YrCreated , YrShp < YrCreated ) , YrCreated ,
AND ( YrFrom < YrCreated , YrShp > YrCreated ) , YrShp ,
YrFrom > YrToday , YrToday ,
YrFrom ) ,
MONTH( tbl_Orders_DatesDays[BILLFROM_DATE] ) ,
DAY( tbl_Orders_DatesDays[BILLFROM_DATE] )
)
The line w/ "ABS" is where I continually get errors, if I were to remove that logic part the measure completes.
The most common error I receive is: "An argument of function 'DATE' has the wrong data type or the result is too large or too small."
Thanks for the help.
So FORMAT returns text I don't know why you are using that in that formula.
It is one of the attempts to correct for the data type, since I am uncertain as to how to get around it. I took shots in the dark.
HI @DataUsurper,
As Greg_Deckler said, current format function only support to return text values, so you can't use it to convert text to number. (you will get number value format text values)
You can try to use the following measure formula if it suitable for your scenario:
Test Begin Date =
VAR billFrom =
MAX ( tbl_Orders_DatesDays[BILLFROM_DATE] )
VAR YrFrom =
YEAR ( billFrom )
VAR YrCreated =
YEAR ( MAX ( tbl_Orders_DatesDays[CREATED_DATE] ) )
VAR YrShp =
YEAR ( MAX ( tbl_Orders_DatesDays[SHIP_DATE] ) )
VAR _year =
IF (
ABS ( YrFrom - YrCreated - 5 ) > 5,
YrShp,
IF (
2 * YrCreated > YrShp + YrFrom,
YrCreated,
IF (
YrFrom < YrCreated
&& YrShp > YrCreated,
YrShp,
MIN ( YEAR ( TODAY () ) + 1, YrFrom )
)
)
)
RETURN
DATE ( _year, MONTH ( billFrom ), DAY ( billFrom ) )
If above not help, I'd like some dummy data to test. It is hard to test a Dax formula without any sample data.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |