Forum Discussion

DataUsurper's avatar
DataUsurper
Helper II
6 years ago

Error on DateDiff Measure w/ Variables

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.

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

    • DataUsurper's avatar
      DataUsurper
      Helper II
      6 years ago

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

        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