Calculating Past Active Matters

[JMB82]

Hello,

Needing some help. I am trying to determine the amount of past active matters. What I mean by past active matters is matters that were open in previous fiscal years and not yet been resolved. In my data set I have an Open Date and Resolution Date, so for Past Active, the open date would need to be prior to the selected fiscal year and the resolution date would need to be blank.

Thanks!

[v-cherch-msft]

Hi @JMB82 

You may create a slicer table to select the year.Then create a measure like below.Attached sample file for your reference.

Measure =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Year]
            = SELECTEDVALUE ( 'Table'[Year] ) - 1
            && Table1[Resolution Date] = BLANK ()
    )
)

Regards,

[JMB82]

Thanks for your response Cherie Chen. Not quite exactly what I need. Here is what my data looks like and what I have written so far.  The formula below isn't correct, it just brings back 1 number for all the years.

Matter list

Dates Table

FYPastActiveMatters = CALCULATE(

    DISTINCTCOUNT('�All Matters FY'[Matter #]),

    FILTER (

        ALL ( 'Dates'[Date] ), Year(Dates[Date] ) <= MAX ('Dates'[FYKEY] )), ISBLANK('�All Matters FY'[Resolution Date]))

[v-cherch-msft · Accepted solution]

Hi @JMB82 

Please check below measure.If it's not your case,could you simplified your sample data and explain more about your expected output?For further,please refer to How to Get Your Question Answered Quickly

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Matters] ),
    FILTER (
        Table1,
        Table1[Open Date] <= MAX ( Dates[Date] )
            && Table1[Resolution Date] = BLANK ()
    )
)

Regards,