cancel
Showing results for
Did you mean:
Frequent Visitor

## How to subtract last month value to current month value

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: How to subtract last month value to current month value

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Microsoft

## Re: How to subtract last month value to current month value

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thank's Yuliana!

Frequent Visitor

## Re: How to subtract last month value to current month value

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!

Announcements