Forum Discussion

jochendecraene's avatar
jochendecraene
Helper V
3 years ago
Solved

Difference between rows with multiple ID's and types

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!

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      jochendecraene
      Helper V
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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