Forum Discussion

user900's avatar
user900
Helper II
2 years ago
Solved

Matrix help when date filter is applied

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 S...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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

amitchandak's avatar
amitchandak
Super User
2 years ago

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