How do I find the last day of EVERY month for a full year

Question

I have a report that has rows for each of the next 365 days (starting today). The first column has the date incrementing from today to the next 365 days and the remaining columns are blank. In one of the blank columns I want to look at the date value in column 1 and if it is a "last day of the month" add a value to that row from a different dataset. The following code works for the last day of the current month but the rest of them no...

=LOOKUP(Fields!THEDATE.Value, (DateSerial(Year(Now), Month(Now), "1").AddMonths(1).AddDays(-1)), Fields!lot_size.Value, "END_OF_MONTH")

Answer 1

Calculate the next day from this one and if the months are different, then the date is the last day of the month:

=IIF(Month(Fields!THEDATE.Value) <> Month(DateAdd(DateInterval.Day, 1, Fields!THEDATE.Value)), 
    LOOKUP(Fields!THEDATE.Value, Fields!THEDATE.Value, Fields!lot_size.Value, "END_OF_MONTH"),
    Nothing)

Based on the comments, there is nothing to look up so the lot_size can just be accessed with FIRST like so:

=IIF(Month(Fields!THEDATE.Value) <> Month(DateAdd(DateInterval.Day, 1, Fields!THEDATE.Value)), 
    FIRST(Fields!lot_size.Value, "END_OF_MONTH"), 
    Nothing)