Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Delta (Differential) from the first category value

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!

Need Help

3 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?