Calculated column returning a single value based on filters from two other columns

[Anonymous]

Hello,

I would like to make an operation (substraction) on values from a same column and then show it on a different column.

I developped a small example for better understanding of what I'm trying to do:

Raw Data:

Tool #	Operation	Start
Tool 1	A	5/12/2019
Tool 1	B	5/14/2019
Tool 1	C	5/18/2019
Tool 2	A	5/15/2019
Tool 2	B	5/18/2019
Tool 2	C	5/25/2019

What I'm trying to calculate:

Tool #	Turn Around Time
Tool 1	6
Tool 2	10

TAT = Diff between start time of operation C and start time of operation A

I could work on my raw data (excel spreadsheet) directly to work this out but I wanted to know if I could do something in POWER BI directly

Thanks for the help

Sebastien

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

For your requirement, please create the measure below.

Measure =
VAR s_a =
    CALCULATE (
        MAX ( 'Table1'[Start] ),
        FILTER ( ALLEXCEPT ( 'Table1', Table1[ool #] ), 'Table1'[Operation] = "A" )
    )
VAR s_c =
    CALCULATE (
        MAX ( 'Table1'[Start] ),
        FILTER ( ALLEXCEPT ( 'Table1', Table1[ool #] ), 'Table1'[Operation] = "C" )
    )
RETURN
    DATEDIFF ( s_a, s_c, DAY )

Here is the output.

Best Regards,

Cherry