Forum Discussion

Hoszi's avatar
Hoszi
Frequent Visitor
6 years ago
Solved

Calculate row diffence within a table with DAX

Hi,   I'd like to write a DAX code for the column Difference (see the method in calculate) within a table, using row filtering. I try with DAX function EARLIER, FILTER, DATEADD, but I couldn't find...
  • Hoszi's avatar
    Hoszi
    6 years ago

    Finally, I find the good calculation.

     

    DailyChange = 
VAR Yesterday = DATEADD(Table[Date]; -1; DAY)
RETURN
    Table[Count (A)] - 
    CALCULATE(
        MAXX(Table;Table[Count(A)]);      
        Table[Date] <= Yesterday
    )
az38's avatar
az38
Community Champion
6 years ago

Hi Hoszi 

if you have one row per day try a measure

Difference = 
var _prevDate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date] < SELECTEDVALUE('Table'[Date])))
return
IF(ISBLANK(_prevDate), BLANK(), SELECTEDVALUE('Table'[Count (A)]) - LOOKUPVALUE('Table'[Count (A)],'Table'[Date],_prevDate))
Hoszi's avatar
Hoszi
Frequent Visitor
6 years ago

Hi az38,

I think your logic is good, but I'd like to use the calculation for a new column, so the SELECTEDVALUE funcion doesn't work.

 

Thx

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Create one index column and then using lookuovalue function you can the previous or next row value by adding or subtracting 1 from index.

    Prev=
    Var current=table[value]
    Var previous=lookupvalue (table[value],table[index],table[index]-1)
    Return
    Current-previous

    Thanks
    Pravin