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

[H3nning]

Hi, I know there are several topics about that, but yet I failed to transfer the solutions to my problem. Hope you can help.

I have a calendar table Date, with the column Date. In the same table I have a column [Future Date], which is 1 from the past until today and 0 for future dates.

I build a matrix with two columns. First column is Date[Date] and the second ist:

VAR Before =

DATE(

   YEAR(Min(Date[Date]))-1

   ,MONTH(Min(Date[Date]))

   ,DAY(Min(Date[Date]))

)

Return

Before

So far everything works as expected. In the second column I always see start of the same peroid one year before. If I drill down to month it works for month, if I drill up to years, it works for years. I did the same thing for the end of the period, also works fine, but that is not relevant I think.

But I see all the dates in my matrix, even in the future. I only want to see dates until now, so the last slice of data should always end with the 16th of may 2025. Therefore I added [Future Date] to the filter pane and filtered on 0. then I get the error. Why???

The error seems to be related with the calculation of the year:  YEAR(Min(Date[Date]))-1. When I leave out the "-1" there is no error. But I don't get why?! Min calculates a date as result. From this date the year is extracted as integer and 1 ist substracted. Why cant I make a date of of that year? When I only return YEAR(Min(Date[Date]))-1 I get the correct integer of the previous year. I dont get why I cant make a date oout of that or why this should be blank in any case...

Thank you in advance!

[v-hashadapu]

Hi @H3nning , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!

[v-hashadapu]

Hi @H3nning ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @H3nning ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[MFelix]

Hi @H3nning ,

Not sure but the code you supplied is it a measure or a column? Also I'm confused about the question you refer of teh column for future dates with 0 and 1, in terms of calculation because if you have this column then you can add it has a filter to the matrix and make sure that it will only return the 1 values so past dates.

Can you please explain a litle bit better the question and what you are doing and expected result so I can understand the final result and what is happening.

[H3nning]

Hi, of course. It is a measure.

What I do is exactly what you mentioned. I use the Future Date column to filter the Date[Date] column in my matrix. That works fine as long as I dont put the measure in. 

So without the Filter the Matrix on year level looks like this:

Year

2022

2023

2024

2025

2026

2027

When I put in the future date it looks like this:

Year

2022

2023

2024

2025

So far so good. But the measure brings back 2026 and 2027 to the matrix, but not the data behind it. That is why the term YEAR(Min(Date[Date]))-1 returns -1 for those years. Why are these years coming back, when putting in the measure as a second column?

[v-hashadapu · Accepted solution]

Hi @H3nning , Thank you for reaching out to the Microsoft Community Forum.

Please try below:

PreviousYearDate =

VAR MinDate = MIN(Date[Date])

RETURN

IF(

    NOT ISBLANK(MinDate) && CALCULATE(MAX(Date[Future Date]), Date[Date] = MinDate) = 1,

    DATE(

        YEAR(MinDate) - 1,

        MONTH(MinDate),

        DAY(MinDate)

    ),

    BLANK()

)

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[H3nning]

I could solve it myself, but I don't know why this happens. Maybe someone can explain?

The result of YEAR(Min(Date[Date]))-1 for future dates is -1, which cannot be converted into a year in the date function. But why is this even calculated for future dates? Shouldnt these be filtered out and therefor no calculation for them takes place?