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]),           ...
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

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

  • jochendecraene's avatar
    jochendecraene
    Helper V
    3 years ago

    thank you very much Anonymous