An Argument of Function Date has wrong data type or result is too small or too large Error

Question

Getting the error

An Argument of Function Date has wrong data type or result is too small or too large

when the following measure is included in any graph. However, the Value is shown properly in KPI.

LastYear =
VAR lastFromDate =
    DATE ( YEAR ( [From_Date] ) - 1, MONTH ( [From_Date] ), DAY ( [From_Date] ) )
VAR lastToDate =
    DATE ( YEAR ( [To_Date] ) - 1, MONTH ( [To_Date] ), DAY ( [To_Date] ) )
RETURN
    IF (
        [DateDiff] > 365,
        0,
        SUMX (
            FILTER (
                ALL ( WUSA_CAL_DIM ),
                WUSA_CAL_DIM[End_Date] >= lastFromDate
                    && WUSA_CAL_DIM[End_Date] <= lastToDate
            ),
            [Sales_Value]
        )
    )

I want to show last year sales based on from and to date in the slicer. Removing -1 solves the problem but it doesn't show previous year sales in that case which is needed.

Frostytheswimmer Frostytheswimmer · Accepted Answer · 2019-06-25T22:05:21

I think this has to do with a lack of context around the [From_Date] and [To_Date] in your Variables. However, without access to your source data and not knowing anything about your datamodel due to lack of context I'm making huge assumptions here.

PowerBI ( or rather the DAX ) has no idea which set of dates you want it to use.

Attempt something like:

LastYear =
VAR lastFromDate =
    SELECTEDVALUE(From_Tbl[From_Date], TODAY()) - 365
VAR lastToDate =
    SELECTEDVALUE(To_Tbl[To_Date], TODAY()) - 365
RETURN
    IF (
        [DateDiff] > 365,
        0,
        SUMX (
            FILTER (
                ALL ( WUSA_CAL_DIM ),
                WUSA_CAL_DIM[End_Date] >= lastFromDate
                    && WUSA_CAL_DIM[End_Date] <= lastToDate
            ),
            [Sales_Value]
        )
    )

An Argument of Function Date has wrong data type or result is too small or too large Error

1 Answers