Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LASIESTA
Regular 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
v-yulgu-msft
Employee
Employee

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
v-yulgu-msft
Employee
Employee

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.

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!

Thank's Yuliana!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.