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.

DateNameCodeValue
01/01/2021Water meter223530000
01/01/2021Electricity meter33581235
01/01/2021Contador de gas6589232
01/01/2021Test9999Yes
02/01/2021Water meter223530256
02/01/2021Electricity meter33581302
02/01/2021Contador de gas6589236
03/01/2021Water meter223530423
03/01/2021Electricity meter33581406
03/01/2021Contador de gas6589238
04/01/2021Test9999No
04/01/2021Water meter223530500
04/01/2021Electricity meter33581455
04/01/2021Contador de gas6589236

I need to add a column with the water consumption of each day, the electricity consumption and the gas consumption. I do not know how I could filter it to get the result to appear on 02/01/2021 (30256-30000), that is, 256, the consumption of that day, something such that

DateNameCodeValueCons waterCons ElectCons gas
01/01/2021Water meter223530000
01/01/2021Electricity meter33581235
01/01/2021Contador de gas6589232
01/01/2021Test9999Yes
02/01/2021Water meter223530256256
02/01/2021Electricity meter33581302 67
02/01/2021Contador de gas6589236 4
03/01/2021Water meter223530423167
03/01/2021Electricity meter33581406 104
03/01/2021Contador de gas6589238 2
04/01/2021Test9999No
04/01/2021Water meter22353050077
04/01/2021Electricity meter33581455 49
04/01/2021Contador de gas6589245 7

Obviously, it is just a sample, the database consists of more than 200 different codes, and there is data from several years.

Could you help me? Thank you very much in advance!

  • 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

2 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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