Matrix counting rows between two dates

[Anonymous]

Hey all,

I have an accumulating fact table with one row for each "Request". Each row has 'RequestCreateDate' and 'ScreeningCompleteDate' as well as another column 'RequestStatus' that indicates whether the Request passed or not. 

I'm looking to create a matrix with a date index hierarchy and some measures:

• Requests In Queue = Requests where the date index is between the 'RequestCreateDate' and 'ScreeningCompleteDate'
• Requests Passed = Requests with 'ScreeningCompleteDate' == Date index && 'RequestStatus'  == passed
• Requests Failed = Requests with 'ScreeningCompleteDate' == Date index && 'RequestStatus' == failed

I'm having some difficulty getting this to work. Mainly I don't know how to get the date to work and base the measures off of an unrelated date table (as there is no connection to either date column). Any help would be appreciated.

Thanks

[Anonymous · Accepted solution]

@Anonymous  -  These should be close:

Requests In Queue =
VAR date_to_examine =
    MAX ( 'Date'[Date] )
VAR row_count =
    COUNTROWS (
        FILTER (
            '<Your Table>',
                [RequestCreateDate] >= date_to_examine
                && [ScreeningCompleteDate <= date_to_examine
        )
    )
RETURN
    IF ( ISBLANK ( row_count ), 0, row_count ) //If you want to return zeros instead of blank.

Requests Passed =
VAR date_to_examine =
    MAX ( 'Date'[Date] )
VAR row_count =
    COUNTROWS (
        FILTER (
            '<Your Table>',
             [ScreeningCompleteDate] > date_to_examine
             && [RequestStatus] = "passed"
        )
    )
RETURN
    IF ( ISBLANK ( row_count ), 0, row_count ) //If you want to return zeros instead of blank.

Cheers!

Nathan

Cheers!

Nathan