Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtracting Not Working as Expected

Hello -

 

I am working on subtracting Current Data with Past Data using a unique ID to find the last historic value for each unique item. I have successfully calculated the previous value by using the following DAX function:

 

Previous Data = 
'Table'[Current Data]
    - MAXX(
        FILTER('Table',
            'Table'[Index]
            = EARLIER('Table'[Index]) - 1
            && 'Table'[UID] = EARLIEST('Table'[UID])
        ),
        'Table'[Current Data]
    )

 

 

Now that I have this value, I want to subract the each individual row from 'Table' [Current Data] from 'Table' [Previous Data] to get a delta/change value for each row. I used the following as an example:

 

Delta = 'Table'[Current Data] - 'Table'[Previous Data]

 

 

However, when using the above "Delta" calculation all of the values result as "0", and I cannot understand why this is happening.

Below is an example of what I would expect to happen with the delta calculation:

UIDDateCurrent DataPrevious DataDelta
12/3/2157478532(2785)
22/3/21467221342538
11/5/21853285320
31/3/21941971282291
21/6/2121349024(6890)
21/2/21902490240
312/4/20712871280

 

Thank you in advance for any assistance!

  • Anonymous ,a new column, you can use index in place of date

     

    Diff Previous Data =
    var _max = maxx(filter( 'Table','Table'[UID] = EARLIEST('Table'[UID]) , 'Table'[Date] < EARLIER('Table'[Date])),[Date])
    return
    if(isblank(_max) , 0 , [Current Data] -maxx(filter( 'Table','Table'[UID] = EARLIEST('Table'[UID]) , 'Table'[Date] =_max),[Current Data] ) ) 

2 Replies

  • Anonymous ,a new column, you can use index in place of date

     

    Diff Previous Data =
    var _max = maxx(filter( 'Table','Table'[UID] = EARLIEST('Table'[UID]) , 'Table'[Date] < EARLIER('Table'[Date])),[Date])
    return
    if(isblank(_max) , 0 , [Current Data] -maxx(filter( 'Table','Table'[UID] = EARLIEST('Table'[UID]) , 'Table'[Date] =_max),[Current Data] ) ) 

  • Anonymous's avatar
    Anonymous
    Not applicable

    This seems to have worked perfectly! Thank you!