Solution without extra columns?

[IAM]

Hi all,

Hope you can help me design something without adding extra columns.

I want to see the average days between 3 different statuses per employee.

Between date creation -> planned -> actual

On the X should be name of employee, no problem.

But the Y..

Date created is the basis, so always 0

The other two should be the difference between created and planned

And planned and finish.

What is the best way to do this? A way that I can still drill through. Do I need to make an extra column, or measure, or can I build this directly in a chart?

Thanks!

[v-chenwuz-msft · Accepted solution]

Hi @IAM ,

We assum data looks like this:

create a measure to calculate the average days.

Measure =
VAR _s1 = "creation"
VAR _s2 = "planned"
VAR _s3 = "actual"
VAR _s1_s2 =
    VALUE (
        CALCULATE (
            MAX ( 'Table'[start date] ),
            FILTER ( ALLSELECTED ( 'Table'[status] ), [status] = _s2 )
        )
            - CALCULATE (
                MAX ( 'Table'[start date] ),
                FILTER ( ALLSELECTED ( 'Table'[status] ), [status] = _s1 )
            )
    )
VAR _s2_s3 =
    VALUE (
        CALCULATE (
            MAX ( 'Table'[start date] ),
            FILTER ( ALLSELECTED ( 'Table'[status] ), [status] = _s3 )
        )
            - CALCULATE (
                MAX ( 'Table'[start date] ),
                FILTER ( ALLSELECTED ( 'Table'[status] ), [status] = _s2 )
            )
    )
RETURN
    DIVIDE ( _s1_s2 + _s2_s3, 2 )

Pbix file in the end and hope this  helpful.

Best Regards

Community Support Team _ chenwu zhu

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

[danextian]

Hi @IAM ,

It is difficult to imagine what you're trying to achieve without a sample data.  Please also post your expected or if you were to do it in Excel, what would your formula be?

[IAM]

Hi, thanks I know, the thing is I don't have anything yet, I'm just mapping out how I want to design something.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523