Error on DateDiff Measure w/ Variables

[DataUsurper]

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:

• Format, "general number"
• Value
• Datevalue
• Not wrapping it at all

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.

[Greg_Deckler]

So FORMAT returns text I don't know why you are using that in that formula.

[DataUsurper]

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.

[Anonymous]

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