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

[kb_barge]

Value Shown in Card

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]

))

If the -1 is removed from variable ,it works fine but it shows current year in that case and not previous year.Any alternative solutions also welcome.

[sandeep992]

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

[Arielcchy]

Thanks for the explanation. It helps me to fix my time intelligence table! Thank you so much!

--------------------

My error message was:
"Couldn't load the data for this visual

The query referenced calculated table 'Time Intelligence' which does not hold any data because evaluation of one of the rows caused an error."

Original DAX table:

Time Intelligence =

VAR Today = MAX('Calendar'[Date])

VAR ThisYear = YEAR(Today)

VAR ThisMonth = MONTH(Today)

VAR ThisDay = DAY(Today)

...

// Yesterday

                ADDCOLUMNS(

                    GENERATE(

                    SELECTCOLUMNS({"Yesterday"},"Period",[Value]) ,

                    GENERATESERIES(

                           DATE(ThisYear , ThisMonth , ThisDay-1) ,

                           Today-1

                            )

                    ))

======================================
It only breaks on 1st of the month, so I change the DAX to
"

                // Yesterday

                ADDCOLUMNS(

                    GENERATE(

                    SELECTCOLUMNS({"Yesterday"},"Period",[Value]) ,

                    GENERATESERIES(

                            Today-1,

                            Today-1

                            )

                    ))

"

[v-lili6-msft · Accepted solution]

hi, @kb_barge 

It seems that in another forum there is an exactly same post and it has been solved, Is that your post?

https://stackoverflow.com/questions/56761607/an-argument-of-function-date-has-wrong-data-type-or-res...

If not you could refer to it.

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

As the solution, the problem is that there is no row context in measure for 

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

you could just add MAX/MIN/SELECTEDVALUE as below to solve it

VAR lastFromDate = DATE(Year(MAX([From_Date])) -1 , Month(MAX([From_Date])), Day(MAX([From_Date])))

VAR lastToDate = DATE(Year(MAX([To_Date])) -1 , Month(MAX([To_Date])), Day(MAX([To_Date])))

Best Regards,

Lin

[ArunkumarK]

Hi,

I am also getting the same error and the measures are working fine with other reports. I am wondering why its not working with same date format, same logic and same measures.

I am trying to calculate the rolling 12 months based on the selection in the slicer.

e.g. If Aug is selected then the values displayed will be from before last 12 months before august.

Rolling12Months =

Var CurentMth = MAX(Master[Date])-1

Var PreviousMth = DATE(YEAR(CurentMth),MONTH(CurentMth)-12,DAY(CurentMth))

Var Result = CALCULATE(SUM(Summary[Unique Endpoint]),FILTER(Summary,Summary[Date]>=PreviousMth && Summary[Date]<=CurentMth))

Return Result

Thanks in advance,

Arun