Filtering Confusion!

[Malfunction]

Hi

I have created a matrix report in Power Bi Desktop to display student weekly attendance for the last 5 weeks, so setup is as follows: Rows - Student Name,

Columns - Week Commencing,

Values - % Attendance (a simple measure %Attendance = DIVIDE(SUM('Weekly Attendance'[PRESENT_MARKS]),SUM('Weekly Attendance'[ALL_MARKS]))

The basic matrix works fine but I want to only show data for students that have less than 85% Attendance for the last 2 weeks with the added complication that I only want to include weeks when the school is open.  So I have written a little SQL that aggregates the student attendance by week & course (I'm using direct query for the matrix) and created 2 extra columns that show the Sum of the present marks & all marks just for the last 2 weeks (excluding the holidays).  I have created a measure exactly the same as the one above but for the columns with the last 2 weeks attendance & tried to use that as a visual filter to display only those students with less than 85% attendance.  However, this does not work and produces the error 'Couldn't load the data for this visual - Conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value'.  The error message doesn't make any sense but should I able to filter like this or am I tackling this completely the wrong way?  Would be very helpful if someone could explain how I could best filter the visual for this scenario.

Data from query looks like this:

Student Name  Week Commencing   Present Marks   All Marks  PresentMarksLast2Wks  AllMarksLast2Wks

Fred                  05-12-2016                10                      12             10                                    12

Fred                  12-12-2016                10                      13             10                                    13

Fred                  28-11-2016                11                       18             0                                      0

[v-ljerr-msft]

Hi @Malfunction,

Based on my research, it's more like an error that generated from SQL server. And the problem may be related to the time zone setting. Here is a similar thread for your reference.

Regards

[Malfunction]

Thanks, clearly hadn't got my head screwed on yesterday, definitely something it doesn't like with the week commencing date data as the filter works if I change the columns to use week number instead of wc date.  I'll look into that although I'm sure the data is all valid at least.  Just need to work out how to filter on the total of the 2 weeks now!