1

I have been trying to make a new table and want to use the date of the existing data. Can anyone help me understanding what is wrong with this formula? 

dDate = CALENDAR(DATE(YEAR(MIN(Ftransactions[Date])),1,1),DATE(YEAR(MAX(Ftransactions[Date])),12,31))

The error which keeps popping up after pressing enter is:

An argument of function 'YEAR' has the wrong data type or the result is too large or too small. If the argument is expected to be a date, that date must be between January 1, 100 and December 31, 9999.

Paul White
  • 94,921
  • 30
  • 437
  • 687
alphabounce_1228
  • 11
  • 2

1 Answers1

0

In your case the MIN and MAX are returning values that are not recognized as valid dates by the YEAR function. In other words, Ftransactions[Date] contains null values, text, or dates outside the valid range January 1, 100 to December 31, 9999.

dDate = 
VAR ValidDates = FILTER(Ftransactions, 
    NOT(ISBLANK(Ftransactions[Date])) && 
    ISERROR(YEAR(Ftransactions[Date])) = FALSE && 
    Ftransactions[Date] >= DATE(100, 1, 1) && 
    Ftransactions[Date] <= DATE(9999, 12, 31)
)

VAR MinDate = IF(
    ISEMPTY(ValidDates), 
    DATE(YEAR(TODAY()), 1, 1), 
    MINX(ValidDates, Ftransactions[Date])
)


VAR MaxDate = IF(
    ISEMPTY(ValidDates), 
    DATE(YEAR(TODAY()), 12, 31), 
    MAXX(ValidDates, Ftransactions[Date])
)


RETURN
CALENDAR(
    DATE(YEAR(MinDate), 1, 1),
    DATE(YEAR(MaxDate), 12, 31)
)