Delta (Differential) from the first category value

[Anonymous]

Hi all,

I'm trying to come up with a DAX expression to create a column with the delta from the first category value.

It's very easy to show in this picture from Excel:

I have columns "Hour", "Alias" and "Value". I need "Delta".

Thank you!

[az38 · Accepted solution]

Hi @Anonymous 

try a column

Column = 
var _firstHour = CALCULATE(MIN(Table[Hour]), ALLEXCEPT(Table, Table[Alias]) )
RETURN
Table[Value] - CALCULATE(MIN(Table[Value]), ALLEXCEPT(Table, Table[Alias]), Table[Hour] = _firstHour )

[Greg_Deckler · Accepted solution]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[az38 · Accepted solution]

Hi @Anonymous 

try a column

Column = 
var _firstHour = CALCULATE(MIN(Table[Hour]), ALLEXCEPT(Table, Table[Alias]) )
RETURN
Table[Value] - CALCULATE(MIN(Table[Value]), ALLEXCEPT(Table, Table[Alias]), Table[Hour] = _firstHour )

[Anonymous]

This works,

However, I noticed that the _firstHour was being taken from way earlier than what I anticipated. 

I did some digging and I found out that I need to include two more variables from another table to truncate the earliest hour. Something like this, which is not working:

FirstHour = CALCULATE(

    MIN('Table'[Hour]), 

    ALLEXCEPT(  

        'Table',

        'Table'[Alias]

    ),

    ALLEXCEPT(

        'AnotherTable',

        'AnotherTable'[Iteration]

    ),

    ALLEXCEPT(

        'AnotherTable',

        'AnotherTable'[Client]

    )

)

They are related by Hour: as Many (Table) to One (AnotherTable)

Any idea?