Forum Discussion

DataUsurper's avatar
DataUsurper
Icon for Helper II rankHelper 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 issu...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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

DataUsurper's avatar
DataUsurper
Icon for Helper II rankHelper 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