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 the right solution.

 

(1)Count (A)DateDifferenceCalculate
(2)02020.03.03  
(3)22020.03.042=A3-A2
(4)22020.03.050=A4-A3
(5)22020.03.060=A5-A4
 42020.03.072...
 72020.03.083 
 92020.03.092 
 92020.03.100 
 132020.03.114 
 132020.03.120 
 192020.03.136 

 

Thanks,

András

  • 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
    )

5 Replies

  • 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
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    This is a calculation for Power Query, not for DAX.

    Best
    D
  • Hoszi's avatar
    Hoszi
    Frequent Visitor
    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
    )