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

[Pri]

I am getting below mentioned error with the following DAX statement:

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

Sales Avg1 =

VAR EndD = CALCULATE(MINX(Query2,Query2[DATE1]))

Var StartD = date(year(EndD)-1,MONTH(EndD),DAY(EndD))

return AVERAGEX(FILTER(Query1,DATESBETWEEN(Query1[Date],EndD,StartD)),Query1[Net_Revenue])

Can someone look into it please?

[Zubair_Muhammad · Accepted solution]

@Pri 

Try this alternate

Sales Avg1 =
VAR EndD =
    CALCULATE ( MINX ( Query2, Query2[DATE1] ) )
VAR StartD =
    DATE ( YEAR ( EndD ) - 1, MONTH ( EndD ), DAY ( EndD ) )
RETURN
    AVERAGEX (
        FILTER ( Query1, Query1[Date] <= EndD && Query1[Date] >= StartD ),
        Query1[Net_Revenue]
    )

[Pri]

This has worked!

Many thanks 🙂

[Zubair_Muhammad]

@Pri 

The 2nd argument of FILTER must return a BOOLEAN expression (true or false)

DATESBETWEEN(Query1[Date],EndD,StartD) returns a date table

Thats why it throws an error