'd like to get help with DAX

[kormosb]

Hi,

I have an issue/bug list from testing a system where I'd like to calculate the average days of an issue being in the same status.

I managed to calculate it with EARLIER:

Days of being in the same status = DATEDIFF([Modify date], MINX(FILTER('Issue list','Issue list'[Issue ID]=EARLIER('Issue list'[Issue ID]) && [Modify date]>EARLIER([Modify date])),[Modify date]),DAY)

Unfortunately I can't handle those kind of issues, where we've modified the issue somehow (in that case added a responsible person) but haven't changed the status (stayed "new"), the average calculates wrong:

Please find attached the PBI file for the data and example.

Thanks in advance,

Kind regards,

Benjamin

[v-xicai]

Hi @kormosb ,

You may create measure like DAX below.

Measure 3 =
CALCULATE (
    AVERAGE ( 'Issue list'[Days of being in the same status] ),
    FILTER (
        ALLSELECTED ( 'Issue list' ),
        'Issue list'[Status] = MAX ( 'Issue list'[Status] )
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kormosb]

Hi @v-xicai 

Thanks for the idea, but it doesn't solve my original problem.

As you can see in the picture, your measure 3 calculates the average (23,33), but in thatcase I should get 32, as you can see in the explanation in the red text box.

[v-xicai · Accepted solution]

Hi @kormosb ,

You may change the measure like Measure 3 below, which uses the SUM function. And if the 'Issue list'[Days of being in the same status-average] is a measure , then you can change it like Measure 4 below.

Measure 3 =
CALCULATE (
    SUM( 'Issue list'[Days of being in the same status-average] ),
    FILTER (
        ALLSELECTED ( 'Issue list' ),
        'Issue list'[Status] = MAX ( 'Issue list'[Status] )
    )
)



Measure 4 =
CALCULATE (
    SUMX( 'Issue list', [Days of being in the same status-average] ),
    FILTER (
        ALLSELECTED ( 'Issue list' ),
        'Issue list'[Status] = MAX ( 'Issue list'[Status] )
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kormosb , Try a measure like this

Measure = AVERAGEX(values('Issue list'[Status]),sumX(SUMMARIZE('Issue list','Issue list'[Status],'Issue list'[Modify date],"_1",AVERAGE('Issue list'[Days of being in the same status])),[_1]))

[kormosb]

Hi,

Thanks, we came closer solving the problem, it is working if I filter for one issue/bug.

Although, when I want to see the average for all the issues (remove the filter) the measure is adding the numbers the wrong way:

So generally, I would like to calculate the average days of an issue being in the same status. So for example, on average the issues are in "New" status for 28,5 days, in "Waiting" status 11,2 days, and so on for all the statuses. Maybe my original calculated column is not good for this calculation.

I wonder if you can help me with this @amitchandak ? (I updated the PBI file)

Thanks,

Benjamin