Monthly consumption of meters

[christobal96]

Hello, 

I would like to calcultate consumption of differents meters using their monthly index. 

An exemple of the raw data would be: 

Meters	Date	index
Cpt1	01/03/2015	12
Cpt2	01/03/2015	24
Cpt1	01/04/2015	18
Cpt2	01/04/2015	32
Cpt1	01/05/2015	22
Cpt2	01/05/2015	39

And the result, i'm expecting is: 

Meters	Date	Consumption
Cpt1	01/03/2015	6
Cpt1	01/04/2015	4
Cpt2	01/03/2015	8
Cpt2	01/04/2015	7

The Consumption is the difference between 2 index of consecutive months.

I hope, this is clear enough. 

Thanks you by advance for help.

[v-yulgu-msft]

Hi @christobal96,

Suppose the original table is called 'Monthly consumption', you could create a measure like below:

consumption =
IF (
    MAX ( 'Monthly consumption'[Date] )
        = MAXX (
            ALLEXCEPT ( 'Monthly consumption', 'Monthly consumption'[Meters] ),
            'Monthly consumption'[Date]
        ),
    BLANK (),
    CALCULATE (
        MAX ( 'Monthly consumption'[index] ),
        FILTER (
            ALLEXCEPT ( 'Monthly consumption', 'Monthly consumption'[Meters] ),
            'Monthly consumption'[Date]
                = MAX ( 'Monthly consumption'[Date] ) + 1
        )
    )
        - MAX ( 'Monthly consumption'[index] )
)

Add this measure into a Table visual.

Best regards,
Yuliana Gu

[Anonymous]

Hello,

How can I do that in the query editor?

What steps do I have to take to get the same result.

I tried the steps below but it didn't work.

I made two columns year and month based on date (1 date each month)

I made two columns min() and max() based on the meter value.

Grouped by Year and kind of meteringpoint

Subtract min() and max()

Kind regards,

Mark

[christobal96 · Accepted solution]

Thanks a lot for this answer. 

There was just a little change I had to do so it's work on my computer: 

Instead of 

 = MAX ( 'Monthly consumption'[Date] ) + 1

Which incremente the day, I had to write:

= DATE(YEAR(MAX ( 'Monthly consumption'[Date] ));MONTH(MAX ( 'Monthly consumption'[Date] ))+1;1)

So the final measure formula is: 

consumption =
IF (
MAX ( 'Monthly consumption'[Date] )
= MAXX (
ALLEXCEPT ( 'Monthly consumption'; 'Monthly consumption'[Meters] );
'Monthly consumption'[Date]
);
BLANK ();
CALCULATE (
MAX ( 'Monthly consumption'[index] );
FILTER (
ALLEXCEPT ( 'Monthly consumption'; 'Monthly consumption'[Meters] );
'Monthly consumption'[Date]
= DATE(YEAR(MAX ( 'Monthly consumption'[Date] ));MONTH(MAX ( 'Monthly consumption'[Date] ))+1;1)
)
)
- MAX ( 'Monthly consumption'[index] )
)

Thanks you Yuliana Gu !