Forum Discussion

IAM's avatar
IAM
Helper III
4 years ago
Solved

Solution without extra columns?

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!

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

4 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.

  • IAM's avatar
    IAM
    Helper III
    4 years ago

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

     

     

  • danextian's avatar
    danextian
    Super User
    4 years ago

    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?