cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LASIESTA Frequent Visitor
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 v-yulgu-msft
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
    )

1.PNG

 

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.

View solution in original post

3 REPLIES 3
Microsoft v-yulgu-msft
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
    )

1.PNG

 

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.

View solution in original post

LASIESTA Frequent Visitor
Frequent Visitor

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

Thank's Yuliana!

LASIESTA Frequent Visitor
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!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)