Forum Discussion
Calculating Past Active Matters
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!
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,
3 Replies
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,
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]))
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,
