Solved: Tickets carried over by quarter

[bwiley]

I am struggling with a measure that will tell me opened tickets carried over by quarter. The measure I have will tell me how many tickets opened in that quarter were carried over but does not include tickets still open from the previous quarter.

For example if I have 20 tickets still open end of 2024-Q1 and I also have 5 tickets till open from Q4-2023 that were not resolved in Q1 I should get 25. I just get the 20. I know I am filtering something wrong but not sure how to fix it. 

------------------------------------

'Calendar' is my date table.

--------------------------------------

Measure:

Carryover Tickets =

VAR _min =

    MIN ( 'Calendar'[Date] )

VAR _max =

    MAX ( 'Calendar'[Date] )

VAR _result =

    CALCULATE (

        DISTINCTCOUNT ( sc_task[RITM / CNTR] ),

        FILTER (

            sc_task,

            sc_task[Open Date] <= _max

                && ( sc_task[Closed Date] >= _max

                || sc_task[Closed Date] = BLANK() )

        )

    )

RETURN

_result

-------------------------------------

Bar chart:

-------------------------------------------------------------------------------------------------------

Snippet of how my table data looks:

RITM/CNTR	Open Date	Closed Date
1	11/1/23	12/31/24
2	12/5/23
3	1/5/24	2/6/24
4	2/1/24	3/1/24
5	2/15/24
6	3/3/24

Thank you!

[Anonymous · Accepted solution]

Hi @bwiley ,

I made a sample for you.

Measure = 

VAR _min =
   MIN ( 'Calendar'[Date] )
VAR _max =
   MAX ( 'Calendar'[Date] )
VAR _result =
    CALCULATE (
        DISTINCTCOUNT ( 'sc_task'[RITM/CNTR] ),
        FILTER (
            ALL(sc_task),
            (sc_task[Open Date] <= _max &&
             sc_task[Closed Date] > _max) ||
             (sc_task[Open Date] <= _max &&
             sc_task[Closed Date]=BLANK())
        )
    )
RETURN
_result

Best Regards,

Wearsky

[bwiley]

I am going to mark the above as the solution since it gave me what I needed after I changed

ALL(sc_task),

to ALLEXCEPT(sc_task),

to account for the filters. 

Thank you very much!

This was my final solution:

VAR _min =
MIN ( 'Calendar'[Date] )
VAR _max =
MAX ( 'Calendar'[Date] )
VAR _result =
CALCULATE (
DISTINCTCOUNT (sc_task[RITM / CNTR] ),
FILTER (
ALLEXCEPT(sc_task,
sc_task[Type2],
sc_task[RITM Status 2],
sc_task[Assignment Group],
sc_task[RITM / CNTR],
ast_work[request_item.opened_at],
'Calendar'[Month Year Filter CLM FY23 Forward],
'Calendar'[Month Year Filter CLM Metrics]
),
(sc_task[Open Date] <= _max &&
sc_task[Closed Date] > _max) ||
(sc_task[Open Date] <= _max &&
sc_task[Closed Date]=BLANK())
)
)
RETURN
_result

[Kedar_Pande]

@bwiley ,

Updated version of your DAX measure:

Carryover Tickets = 
VAR _max = MAX('Calendar'[Date])
VAR _currentQuarterStart = 
CALCULATE(
MIN('Calendar'[Date]), 
FILTER('Calendar', 'Calendar'[Quarter] = QUARTER(_max) && 'Calendar'[Year] = YEAR(_max))
)
VAR _previousQuarterStart = 
EDATE(_currentQuarterStart, -3)
RETURN 
CALCULATE(
DISTINCTCOUNT(sc_task[RITM / CNTR]),
FILTER(
sc_task,
sc_task[Open Date] < _max && 
(sc_task[Closed Date] >= _max || sc_task[Closed Date] = BLANK() || sc_task[Closed Date] > _currentQuarterStart)
)
) +
CALCULATE(
DISTINCTCOUNT(sc_task[RITM / CNTR]),
FILTER(
sc_task,
sc_task[Open Date] < _previousQuarterStart && 
(sc_task[Closed Date] >= _max || sc_task[Closed Date] = BLANK())
)
)

If this helped, a Kudos 👍 or Solution mark would be great!

Cheers,

Kedar Pande

www.linkedin.com/in/kedar-pande

[AllisonKennedy]

@bwiley  the _Min and _Max variables will be impacted by your visual filters, so you may get the result you're looking for if you add an ALLSELECTED to them: 

Carryover Tickets =

VAR _min =
   CALCULATE( MIN ( 'Calendar'[Date] ), ALLSELECTED(Calendar) )
VAR _max =
  MAX ( 'Calendar'[Date] )
VAR _result =
    CALCULATE (
        DISTINCTCOUNT ( sc_task[RITM / CNTR] ),
        FILTER (
            sc_task,
            sc_task[Open Date] <= _max
                && ( sc_task[Closed Date] >= _max
                || sc_task[Closed Date] = BLANK() )
        )
    )
RETURN
_result

I think you still want your MAX to be filtered by the visual, so I didn't add the ALLSELECTED there, but if I misunderstood play around with that.