Problem with FILTER function

[IanR]

Hi,

Is there anything wrong with this formula? I want it to give me the first day of the year in the current filter context but instead it gives me the first date in my dates table:

CALCULATE (MIN(tbl_Dates[FullDate]),
                        FILTER(ALL(tbl_Dates),
                         tbl_Dates[CalendarYear] = YEAR([DayInFilter_Last])
  )
 )

DayInFilterLast is a measure that returns the last date in the current filte: CALCULATE(MAX(tbl_Dates[FullDate])).

I have used this successfully as a starting point for calcualting MTD and YTD figures for this year and last year.

Determining the biginning and the end of the week last year is a bit more complicated and what I was doing wasn't working. The formula above is part of my attempt to work out why.

Thanks

Ian

[mattbrice]

To answer your question as to why you are getting the first date from your dates table is a bit involved.  Someone else did the same thing that I commented on recently and it is a common mistake.  Sorry but I can't remember what posting it was or i'd link to it for a full explanation.  The brief answer is this:

"FILTER(ALL(tbl_Dates),tbl_Dates[CalendarYear] = YEAR([DayInFilter_Last])"

FILTER is an iterator that creates a row context.  [DayInFilter_Last] is a measure that transitions the current row into the filter context making it so the result is always true for every date in your dates calendar.  So MIN then returns the first date in the Dates table. Do this instead and it should work:

CALCULATE (
    MIN ( tbl_Dates[FullDate] ),
    FILTER (
        ALL ( tbl_Dates ),
        tbl_Dates[CalendarYear] = YEAR ( MAX ( tbl_Dates[FullDate] ) )
    )
)

[IanR]

Hi,

Still struggling with this. I have tried to rebuild the model from scratch. I'm actually doing this one in Power Pivot, I hope that doesn't make any difference. I've reached the same point and I'm still getting stuck. Neither of the formulas below work. They both cause the same error: '... A single value for column 'FullDate' in table 'tbl_Dates' cannot be determined ...'.  

I thought I had grasped the row context/filter context issue outlined in matbrice's reply but clearly I haven't.

As before, ThisYear is a measure that takes the MAX value from a slicer based on a disconnected table. I get the same problem when I replace MAX(ThisYear) with a literal value.

YTD_End__TY:=CALCULATE(

MAX(tbl_Dates[FullDate],

FILTER(ALL(tbl_Dates),

tbl_Dates[CalendarYearNum] = MAX([ThisYear])

)

))

YTD_End__TY:=CALCULATE(
   MAX(tbl_Dates[FullDate],
   FILTER(ALL(tbl_Dates),
    tbl_Dates[CalendarYearNum] = 2017
    )
   ))

[mattbrice · Accepted solution]

Don't know if it reason for error, but you have a close parenthesis in wrong spot in both formulas. Need one after MAX function call. Otherwise formula looks fine.

[IanR]

That's done it! Thank you mattbrice.

How do I flag your post as the answer? Am I missing something obvious like I did with brackets in my formulas?

For the record, these now work:

CALCULATE(

                                  MAX(tbl_Dates[FullDate]),

                                  FILTER(ALL(tbl_Dates),

                                                  tbl_Dates[WeekNumber] = [ThisWeek]

                                  )

                 )

CALCULATE(

                                  MAX(tbl_Dates[FullDate]),

                                  FILTER(ALL(tbl_Dates),

                                                  tbl_Dates[WeekNumber] = [ThisWeek]

                                                  &&

                                                  tbl_Dates[CalendarYearNum] = [ThisYear]

                                  )

                 )

[mattbrice]

You should look into using daxformatter.com. It is a free online tool that formats code but also points out syntactical issues like yours. I use it all the time.

[IanR]

Hi matbrice,

 Please don't read my lack of response as a loss of interest. I *think* this is the correct solution but have been unable to confirm.  I had a five minute fiddle which was unsuccessful but hoping to test properly this weekend.

Thanks

[v-huizhn-msft]

Hi @IanR,

Your issue has been resolved? If it has been, please mark the corresponding reply as answer. More people will find workaround clearly.

Thanks,
Angelia

[MFelix]

Hi @IanR,

If you want to calculate the 1st day of.the year and you have the last day probably the easist way is:

DayInFilter_First = DATE(YEAR(DayInFilter_Last);1;1)

Regards
MFelix