An argument of function ‘DATE’ has the wrong data type or the result is too large or too small

Question

_{Tuesday, April 25, 2017 11:52 AM}

Calculation error in the measure ‘tbl_Dates’[DayInFilter_BOM]:

An argument of function ‘DATE’ has the wrong data type or the result is too large or too small.

I cannot understand why I am getting the error above. I am trying to create a report that allows the user to select a week then return various metrics for the selected week, for the calendar month up to the end of the selected week (MTD), for the year up to the end of the selected week (YTD) and compare these with the same figures for the previous year. There doesn’t seem to be a way of getting MTD and YTD figures for the previous year – as far as I can tell the built in functions all give completed months for the previous period, meaning that you end up comparing performance up to March 5^th this year with performance up to March 31^st last year, which isn’t what we want – so I thought I would use slicers based on disconnected tables (thank you to Rob’s book for that trick) to select the report week and year from my dates table, look up the beginning and end dates of that week in the dates table, then create some simple measures based on these dates to create a small set of dates that I can use in DATESBETWEEN calculations.

Great plan – what could go wrong?

Step one went OK, getting the beginning and end dates from my disconnected slicers:

DayInFilter_First :=CALCULATE(MIN(tbl_Dates[FullDate]),

                                                  FILTER(tbl_Dates,

                                                  tbl_Dates[WeekNumber] = [SelectedWeek]

                                                  && tbl_Dates[CalendarYear] = [SelectedYear]))

DayInFilter_Last :=CALCULATE(MAX(tbl_Dates[FullDate]),

                                                  FILTER(tbl_Dates,

                                                  tbl_Dates[WeekNumber] = [SelectedWeek]

                                                  && tbl_Dates[CalendarYear] = [SelectedYear]))

But when try to use one of the measures above in a filter to calculate the first of that date’s month I get the error above. This is the function that throws the error:

DatInFilter_BOM:=DATE(        YEAR([DayInFilter_Last]),

                                                  MONTH([DayInFilter_Last]),

                                                  1)

I can see the DayInFilter_First and DayInFilter_Last values on my pivot. They are fine and change as expected when I make selections from my slicers. I have added two measures which show just YEAR(DayInFilter_Last]) and MONTH(DayInFilter_Last]), placed them on a pivot, and they give the expected years and months. I am at a loss to understand why, when these are combined into a DATE function, an error is thrown.

All replies (1)

_{Friday, April 28, 2017 8:09 AM ✅Answered}

Hi lanRoss,

Based on my understanding, measure [DayInFilter_Last] return different based on different rows. YEAR(DayInFilter_Last]) and MONTH(DayInFilter_Last]) return different values in different rows, right? So you add the 
following measure to pivot and check if it works fine.

DatInFilter_BOM:=DATE(YEAR([DayInFilter_Last]),MONTH([DayInFilter_Last]),1)

Best Regards,
Angelia

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.