Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
kb_barge
Frequent Visitor

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

Value Shown in CardValue 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.
 
 
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

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
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.