Spinning on a DAX Function - average age of cases on a stacked column line chart

[RAC_GAVIN]

Hello:

So conceptually I have a case table with a date opened and date closed.  I have created a visual with the Year Heirarchy from my date table on the X axis and the number of cases opened and closed (stacked columns) with a line chart of the amount of open cases using a measure for all three (newly opened cases, closed cases, and total number of open cases).  

So I want to also have an additional line showing the average age of the open cases.  There is a field in my database for closed age, but obviously that does not get me what I am looking for because I am looking back in time .. so I tried to replicate my open cases measure but am having difficulty .. I believe i have to use an iterative function in this case but it is giving me all kinds of problems on the filter context.  I think I now have a working version of the function, but it is not being adjusted by the selections on slicers in the reports (my other line charts are).

Here is my Open Cases Measure - which seems to be working as intended:

Total No Open Tickets =

Var CurrentDate = Max(CWDates[Date])

Var OpenTickets = CALCULATE(COUNTROWS(v_rpt_Service), All(CWDates[Date]), CWDates[Date] <= CurrentDate, IsBlank(v_rpt_Service[date_closed_dtrel]) || v_rpt_Service[date_closed_dtrel] > CurrentDate)

Return OpenTickets

Here is my (I think) partially working function for the average age of the open tickets .. so I believe the problem is I am removing all the filters from the v_rpt_service with All Function, but when I try to narrow down it complains about my other filters.  I am trying to think like DAX .. so any explanation along with an updated function would be very much appreciated:

Test2 =

Var CurrentDate = Max(CWDates[Date])

Var FirstDayNextPeriod = CurrentDate + 1

Var x = Calculate(AVERAGEX((v_rpt_Service), DateDiff(v_rpt_Service[date_entered_dtrel], FirstDayNextPeriod, DAY)), Filter(All(v_rpt_Service), (v_rpt_Service[date_entered_dtrel] <= CurrentDate && (v_rpt_Service[date_closed_dtrel] > CurrentDate || IsBlank(v_rpt_Service[date_closed_dtrel])))))

Return x

[daXtreme · Accepted solution]

File attached

[RAC_GAVIN]

Thanks for this response.  I will check it out in some time just to see if you did the same thing I eventually did .. I actually got it to work on my own.  My initial approach was right, but I think I may have had an extra paranthesis or something that was causing the intellisense to show me errors were not there.  What I did was the averagex takes in the vrptservice (so that the filters are maintained) and filtered using the calculate  function outside of the iterative averagex function.  The report now seems to be working as intended.  For the community here is my working DAX Function for the measure:

Avg Age Open Tix =

Var CurrentDate = Max(CWDates[Date])

Var FirstDayNextPeriod = CurrentDate + 1

Var x = Calculate(AVERAGEX((ServiceTickets), DateDiff(ServiceTickets[date_entered_dtrel], FirstDayNextPeriod, DAY)), All(CWDates[Date]), CWDates[Date] <= Currentdate, ServiceTickets[date_closed_dtrel] > CurrentDate || IsBlank(ServiceTickets[date_closed_dtrel]))

Return x

[ppm1]

Please try this measure expression instead. Rather than using the whole table in your filter, it is better to filter one column at a time.

Test2 =
VAR CurrentDate =
    MAX ( CWDates[Date] )
VAR FirstDayNextPeriod = CurrentDate + 1
VAR x =
    CALCULATE (
        AVERAGEX (
            v_rpt_Service,
            INT ( FirstDayNextPeriod - v_rpt_Service[date_entered_dtrel] )
        ),
        v_rpt_Service[date_entered_dtrel] <= CurrentDate,
        FILTER (
            ALL ( v_rpt_Service[date_closed_dtrel] ),
            v_rpt_Service[date_closed_dtrel] > CurrentDate
                || ISBLANK ( v_rpt_Service[date_closed_dtrel] )
        )
    )
RETURN
    x

Pat

[RAC_GAVIN]

Hi Pat:  Unfortunately that formula update did not work.  Now it seems to be only performing the averagex against rows that fall within the active join to the date table (IE it is showing me a very small average of only about 10-20 days when I am viewing my stacked column line chart by month) .. the active join to the date table is on date entered so my belief is it is giving such a low figure on the average because it is only summing up the records created in that month and giving the datediff for them to the end of the period (the CurrentDate in my formula and the end of the period is the last day of the month when the chart is showing months).