Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Calculate between rows

Good afternoon Surely it is a very simple problem, but I do not find the right solution. I have a database, which brings me a record of samples as shown in the table. Date Name Code Va...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Syndicate_Admin ,

     

    Please refer this formula:

    cons water = 
var last_date = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Code]=2235))
var last_value = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Date]=last_date&&'Table'[Code]=2235))
return
IF(ISBLANK(last_value),BLANK(),IF('Table'[Code]=2235,'Table'[Value]-last_value))

    For [Cons Elect] and [Cons gas], just need to replace the 'Table'[Code] in the formula.

    Result:

     

    Best Regards,

    Jay

johnt75's avatar
johnt75
Super User
4 years ago

Rather than adding 200 columns I would create a summary table with 1 entry per day per code, like

Summary Table =
SUMMARIZECOLUMNS(
    'Table'[Date],
    'Table'[Code],
    'Table'[Value],
    "Consumption",
        VAR currentDate =
            SELECTEDVALUE( 'Table'[Date] )
        VAR currentCode =
            SELECTEDVALUE( 'Table'[Code] )
        VAR currentValue =
            SELECTEDVALUE( 'Table'[Value] )
        VAR prevValue =
            SELECTCOLUMNS(
                TOPN(
                    1,
                    FILTER(
                        ALL( 'Table' ),
                        'Table'[Code] = currentCode
                            && 'Table'[Date] < currentDate
                    ),
                    'Table'[Date]
                ),
                "@val", 'Table'[Value]
            )
        RETURN
            IF(
                ISNUMBER( currentValue ) && ISNUMBER( prevValue ),
                currentValue - prevValue
            )
)

You will need to make sure that the Value column in your main table is numeric, not text.