Difference between rows with multiple ID's and types

[jochendecraene]

Hi

I've created a measure to calculate the difference between rows:

Variation = VAR Diff = Tabel1[Meter reading] -

                        CALCULATE(SUM(Tabel1[Meter reading]),

                        FILTER(Tabel1, Tabel1[Index.1] = EARLIER(Tabel1[Index])))

                        return

                            if(Diff=VALUE(Tabel1[Meter reading]),

                            0,

                            Diff)

This gives me the following result, wich is correct 

Now, the dataset contains multiple buildings and 2 types

Can someone help me out adjusting the measure so I can get the expected result?

Thnx!

[Anonymous · Accepted solution]

Hi @jochendecraene ,

You can create a calculated column as below to get it:

Consumption = 
VAR _predate =
    CALCULATE (
        MAX ( 'Tabel1'[date] ),
        FILTER (
            'Tabel1',
            'Tabel1'[Building] = EARLIER ( Tabel1[Building] )
                && 'Tabel1'[type] = EARLIER ( Tabel1[type] )
                && 'Tabel1'[ID] = EARLIER ( Tabel1[ID] )
                && 'Tabel1'[date] < EARLIER ( Tabel1[date] )
        )
    )
VAR _pdmeter =
    CALCULATE (
        SUM ( 'Tabel1'[Meter reading] ),
        FILTER (
            'Tabel1',
            'Tabel1'[Building] = EARLIER ( Tabel1[Building] )
                && 'Tabel1'[type] = EARLIER ( Tabel1[type] )
                && 'Tabel1'[date] = _predate
        )
    )
RETURN
    IF ( ISBLANK ( _pdmeter ), BLANK (), 'Tabel1'[Meter reading] - _pdmeter )

Best Regards

[jochendecraene]

thank you very much @Anonymous 

[jochendecraene]

Thank you so much @Anonymous !

Is it possible to give a brief explanation on how this measure works? Any hints on good resources to learn more about this kind of DAX?

[Anonymous]

Hi @jochendecraene ,

As you can see the formula below, there are two variables:

• The variable _predate is used to get the previous date which have the same ID, same building and same type and less than current date
• The variable _predate is used to get the sum of [Meter reading] on the previous date

Consumption = 
VAR _predate =
    CALCULATE (
        MAX ( 'Tabel1'[date] ),
        FILTER (
            'Tabel1',
            'Tabel1'[Building] = EARLIER ( Tabel1[Building] )
                && 'Tabel1'[type] = EARLIER ( Tabel1[type] )
                && 'Tabel1'[ID] = EARLIER ( Tabel1[ID] )
                && 'Tabel1'[date] < EARLIER ( Tabel1[date] )
        )
    )
VAR _pdmeter =
    CALCULATE (
        SUM ( 'Tabel1'[Meter reading] ),
        FILTER (
            'Tabel1',
            'Tabel1'[Building] = EARLIER ( Tabel1[Building] )
                && 'Tabel1'[type] = EARLIER ( Tabel1[type] )
                && 'Tabel1'[date] = _predate
        )
    )
RETURN
    IF ( ISBLANK ( _pdmeter ), BLANK (), 'Tabel1'[Meter reading] - _pdmeter )

You can review the following links to learn DAX:

DAX overview

How to learn DAX

DAX Guide

Best Regards