How to subtract last month value to current month value

[LASIESTA]

Hello, 

I have three colums, timestamp, meter model and meter read (it's a power meter). There's one register per day.

The meter read column it's an accumulative column (because it's a meter so it's always increasing). 

I want to add a new column with the power consumption (current month last value - anterior month last value)

How can i do it?

Thank you

[v-yulgu-msft · Accepted solution]

Hi @LASIESTA,

current month last value - anterior month last value =
VAR currentmonthlastvalue =
    CALCULATE (
        LASTNONBLANK ( Table_1[Meter read], 1 ),
        ALLEXCEPT ( Table_1, Table_1[Time].[Year], Table_1[Time].[MonthNo] )
    )
VAR anteriormonthlastvalue1 =
    CALCULATE (
        LASTNONBLANK ( Table_1[Meter read], 1 ),
        ALLEXCEPT ( Table_1, Table_1[Time].[Year], Table_1[Time].[MonthNo] ),
        FILTER (
            Table_1,
            Table_1[Time].[Year] = EARLIER ( Table_1[Time].[Year] )
                && Table_1[Time].[MonthNo]
                    = EARLIER ( Table_1[Time].[MonthNo] ) - 1
        )
    )
VAR anteriormonthlastvalue2 =
    CALCULATE (
        LASTNONBLANK ( Table_1[Meter read], 1 ),
        ALLEXCEPT ( Table_1, Table_1[Time].[Year], Table_1[Time].[MonthNo] ),
        FILTER (
            Table_1,
            Table_1[Time].[Year]
                = EARLIER ( Table_1[Time].[Year] ) - 1
                && Table_1[Time].[MonthNo] = 12
        )
    )
RETURN
    IF (
        Table_1[Time].[MonthNo] = 1,
        currentmonthlastvalue - anteriormonthlastvalue2,
        currentmonthlastvalue - anteriormonthlastvalue1
    )

Best regards,

Yuliana Gu

[LASIESTA]

Hi!

I just have detected and error. Your solution doesn't work for each meter model, works for a total. Each meter have an independent read. So I need the same but the formula have to take into account the counter model. For example imagine that counter A is a power meter, the counter B es a Water meter and C is a petrol meter. 

Thank you!

[LASIESTA]

Thank's Yuliana!