CODEWITHSUNDEEP
powerbidax
Amey
Amey

Reputation: 53

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

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.

Upvotes: 4

Views: 13919

Answers (3)

Sarvesh Kalyan
Sarvesh Kalyan

Reputation: 11

I came across this issue today, its late but, It has to do with Blank dates present somewhere in the column.

In this Instance, DATE ( YEAR ( [From_Date] ) - 1, MONTH ( [From_Date] ), DAY ( [From_Date] ) ) if From_Date is BLANK, it is one of the reason for the error.

Suggested Alternative, IF( ISBLANK([From_Date]), BLANK(), DATE ( YEAR ( [From_Date] ) - 1, MONTH ( [From_Date] ), DAY ( [From_Date] ) ))

Upvotes: 1

Shaboo 365
Shaboo 365

Reputation: 1

The simple solution for that is to understand are you applying -1 (day before) to the day or to the full date (yyyy,mm,dd) or just to the day (dd). 

For Example: If you are applying -1 to the day and the today is 1st of the month it througn an error as it try to find 1-1 with no date reference where if you apply end of the date that work absolutely fine even for the 1st of the month. 

If you want to get last 12 months dates Correct solution is -

VAR Days = CALENDAR(DATE(YEAR(TODAY())-1,MONTH(TODAY())-1,DAY(TODAY())),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

If you want get the last year same date + 1 day before

VAR Days = CALENDAR(DATE(YEAR(TODAY())-1,MONTH(TODAY())-1,DAY(TODAY()))-1,DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

However this give you an error if you add -1 after the day shown below Wrong approach this thrown an error date is too small or too large

VAR Days = CALENDAR(DATE(YEAR(TODAY())-1,MONTH(TODAY())-1,DAY(TODAY())-1),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

Upvotes: 0

Frostytheswimmer
Frostytheswimmer

Reputation: 718

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]
        )
    )

Upvotes: 3

Related Questions