Matrix help when date filter is applied

[user900]

Here is a sample of my data.  It shows only 2 students of many:

I have a measure to calculate the number of times a student failed a test, cumulating each month

**bleep**.Fail Count =

VAR StartDate = DATE(2023,5,1)

VAR CurrentDate = 'TestRecord'[Report Date]

VAR CurrentStudentID = 'TestRecord'[Student ID]

RETURN

CALCULATE(

    COUNTA('TestRecord'[Student ID],

    FILTER(

        ALL('TestRecord'),

        'TestRecord'[Report Date]<=CurrentDate &&

        'TestRecord'[Report Date]>=StartDate &&

        'TestRecord'[Student ID]=CurrentStudentID &&

        'TestRecord[Fail]="True"

    ))

and a column to show if the student failed more than once

Repeat Fail? = 'Test Record'[Last]>"1" (last represents the maximum **bleep**.Fail Count per student)

In this sample, I expect to see Total Repeat Fails = 2.  My card shows 2, so this works.

But my matrix does not.  It needs to include Student Name, the tests they failed and Fail result.  Here is my expected result:

I also have a slicer for Professor.  When I select George, my matrix shows many more students and should only show 1. 

When I add a visual filter for the last report date, it shows 1 name as expected, but it also filters out the previous test history.  I need to have the history.  I also tried to change the visual filter to show all dates.  This brings back the history, but not the correct number of students that failed more than once.  Any ideas on how to fix this?

Thanks in advance,

~user 900

[amitchandak · Accepted solution]

@user900 , In such case it always better to use date table joined with date of your tbale

example

VAR StartDate = DATE(2023,5,1)
VAR CurrentDate = 'Date'[Date]
RETURN
CALCULATE(
COUNTA('TestRecord'[Student ID],
FILTER(
ALL('Date'),
'Date'[Report Date]<=CurrentDate &&
'Date'[Report Date]>=StartDate ),
Filter('TestRecord', 'TestRecord' [Fail]="True")
))

then filter should work properly

You can also explore the window function

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f