Calculating the previous value in Power BI

Author:Murphy | View: 22370 | Time: 2025-03-22 22:00:24

When we have Meter data, like we can get from our energy or water meters at home, we want to calculate consumption from these values over time. What is very simple with one meter can be complicated if we have multiple meters for different areas, values, etc. Let's see how we can solve this in Power Bi.

Introduction

Most of the time, we work with transactional data.

Each row describes one event (or transaction) from the source system.

Then we have stock data, like the number of units of a specific product in our store, which can change over time.

Or we have meter data, which changes over time, like, for example, the electricity meter at home.

When we want to calculate the consumption, we order the data by the date and time we got the value from the meter and subtract the current value from the previous value. Et voila, we have the consumption.

Now imagine we have multiple houses at different addresses and with multiple meters in each home.

In this case, we must calculate the previous value for each meter and get the correct value.

This poses some challenges when we must do it in Power BI.

By the way, in SQL, we have some techniques to solve this challenge with minimal effort. So, when you have your data in a relational Database, do it there. It's much easier.

So, let's see how we can solve it in Power BI.

I do it once in Power Query and then with DAX.

The Data

I generate the data from my Date table to load the result in a table with the following SQL Query:

DECLARE @dimId int = 2;
DECLARE @value decimal(18,5) = RAND() * 12;

INSERT INTO [dbo].[MeterData]
  ([DateKey]
  ,[Value]
  ,[House]
  ,[Meter])
SELECT [DateKey]
    ,DATEDIFF(dd, '20000101', [Date]) + (DATEDIFF(dd, '20000101', [Date]) * @value%@dimId)
    ,'House ID ' + CAST(([DateKey]%3) + 1 AS varchar(15)) AS [House]
    ,'Meter ID ' + CAST(@dimId - 1 AS varchar(15)) AS [Meter]
  FROM [dbo].[Date]
    WHERE [DateKey] BETWEEN 20210101 AND 20240415;

I execute this query multiple times to get the needed data while setting the variable @dimId to values between 2 and 6.

The result is a list of changing values over time for each Meter ID:

Figure 1 – Data for my scenario (Figure by the Author)

I import this table into Power BI twice and name them:

MeterData_PQ → For the Power Query approach
MeterData_DAX → For the DAX approach

I need these two tables to compare them side-by-side after completion to analyze which approach could be better.

Doing it in Power Query

I found this approach after a little search on the Internet.

I added a link to the original article in the Reference section below.

To ensure that the data is in the right order for the next step, I add a Sorting Expression by House, Meter, and DateKey to ensure that all rows are together:

= Table.Sort(#"Changed Type",{{"House", Order.Ascending}, {"Meter", Order.Ascending}, {"DateKey", Order.Ascending}})

This is the result after sorting the data:

Figure 2 – Table after sorting operation (Figure by the Author)

This pattern is repeated automatically for each nested table.

Now, I use the Group By transformation in Power Query to group all rows for each combination of House and Meter together:

Figure 3 – Group all Value rows together (Figure by the Author)

Now the data looks like this:

Figure 4 – Data after grouping (Figure by the Author)

When I click on a cell for the ValueTable column, I see all rows associated with that row as a nested table:

Figure 5 – Nested table for each combination of House and Meter (Figure by the Author)

The subsequent transformations must be applied to the nested tables. The Power Query Interface doesn't support such operations.

Therefore, I must add the successive transformations as manual steps:

Figure 6 – Add a new step to Power Query (Figure by the Author)

I enter the following expression to add an Index column to the data:

= Table.TransformColumns(
    #"Grouped Rows",
    {{"ValueTable", each Table.AddIndexColumn(_,"Index")}}
    )

This is the result inside the nested table:

Figure 7 – Adding an Index to the nested tables (Figure by the Author)

The Index column is calculated based on the order of the data. This is why we must order the data accordingly before we add this index.

To make this more readable, I rename this step to "AddIndexColumn".

Now, I add another step to get the previous value:

= Table.AddColumn(AddIndexColumn, "PrevValue", each let
    AllDataTable = [ValueTable],
    PrevRowValue = Table.AddColumn(AllDataTable, "PrevValue",
                        each try AllDataTable [Value] { [Index] -1 }
                        otherwise null
                        )

                    in
                    PrevRowValue)

The result of the new column in the nested table(s) is the following:

Figure 8 – Nested table with the previous value (Figure by the Author)

Next, I use the Drill Down transformation to expand the nested table(s) into the original table:

Figure 9 – Drill down into the nested table(s) (Figure by the Author)

Now I have a List with all nested tables. I add a new step to do it with the following expression:

= Table.Combine( #"Drill down PrevValue"
              ,{"DateKey", "House", "Meter", "Value", "PrevValue"}
              )

The result is the table with all original rows but with the additional column "PrevValue".

To complete the task, I can add a new calculated column to detract the column "Value" from "PrevValue" to get the needed consumption:

Figure 10 – Calculate the consumption (Figure by the Author)

Lastly, I must set the correct data type for the new numeric columns to "Decimal Number".

After loading the result into Power BI, I can create a consumption chart per Meter and House:

Figure 11 – Consumption over time for each House and Meter (Figure by the Author)

This is the expected result, and I can start creating a nice report and useful visualizations.

But first, I wanted to show you the approach with DAX.

Doing it in DAX

After seeing how to do it in Power Query, let's do it in DAX.

Here we have two possible scenarios:

Fixed intervals between the readings of the Meters.
Changing interval between the readings.

To get the consumption of the first scenario is easy:

I must identify the row with the previous day's value.
Get the value from that row.
Calculate the consumption.

Let's do it:

I do it by creating two Key columns:

One for the current reading
One for the reading of the previous day (This can be the previous day, week, month, or whichever interval you have).

Due to how I generated my data, I have one reading for every Meter ID.

Therefore, to create the first key column, I create a calculated column with the following expression (ignoring the House column for now):

CurrentKey =
VAR RowKey = FORMAT('MeterData_DAX'[Date], "YYYYMMDD") & "_" & 'MeterData_DAX'[Meter]

RETURN
  RowKey

Note: I use the format "YYYYMMDD" for better readability of the result as it's a general format.

I need the date from the date table for the previous day to apply date calculations, like DATEADD().

Then I can go back by one day:

PreviousKey =
VAR PreviousDate = FORMAT(DATEADD('MeterData_DAX'[Date], -1, DAY), "YYYYMMDD")
VAR RowKey =
      PreviousDate & "_" & 'MeterData_DAX'[Meter]

RETURN
  RowKey

Lastly, I can use LOOKUPVALUE() to get the previous value:

Previous Value = LOOKUPVALUE('MeterData_DAX'[Value]
                            ,'MeterData_DAX'[CurrentKey]
                                ,'MeterData_DAX'[PreviousKey]
                                )

Or I can use CALCULATE() to achieve the same result:

PrevValue =
VAR PreviousKey = 'MeterData_DAX'[PreviousKey]

RETURN
  CALCULATE(
      MAX('MeterData_DAX'[Value])
      ,REMOVEFILTERS('MeterData_DAX')
      ,'MeterData_DAX'[CurrentKey] = PreviousKey
      )

This is the result of these three expressions:

Figure 12 – Results for the previous values on day-by-day readings (Figure by the Author)

But this approach doesn't work with irregular readings.

When I look at my data (including the Houses), I see this:

Figure 13 – Filtered view of the readings by house and Meter (Figure by the Author)

As you can see, there are intervals between the readings.

To get the correct result, I use an approach with two steps:

Get the date of the previous readings.
Get the value for that date.

I create a Measure for the first step:

Previous reading date =
VAR CurrentDate = SELECTEDVALUE('MeterData_DAX'[DateKey])
VAR CurrentHouse = SELECTEDVALUE('MeterData_DAX'[House])
VAR CurrentMeter = SELECTEDVALUE('MeterData_DAX'[Meter])

RETURN
  CALCULATE(MAX('MeterData_DAX'[DateKey])
          ,REMOVEFILTERS('MeterData_DAX')
          ,'MeterData_DAX'[House] = CurrentHouse
          ,'MeterData_DAX'[Meter] = CurrentMeter
          ,'MeterData_DAX'[DateKey] < CurrentDate
          )

First, I store the current Date, House, and Meter in variables.

Then, I calculate the highest value for DateKey while removing all Filters from the table, adding Filters for the Current House and Meter, and including only the DateKey lower than the current DateKey.

With a table with more columns, I might have used a slightly different approach by not removing all filters from the tables but only for the columns I must, for example, only for DateKey, House, and Meter.

But the result is as needed:

Figure 14 – Result of the Measure to get the date of the previous reading (Figure by the Author)

By using Context Transition, I can use this Measure to create a new version of the Previous Key column (I include the House in the expression for the CurrentKey column as well):

PreviousKey =
VAR PreviousDate = [Previous reading date]
VAR RowKey = PreviousDate & "_" & 'MeterData_DAX'[House] & "_" & 'MeterData_DAX'[Meter]

RETURN
  RowKey

Now, I can use the same expression as before to get the previous value based on the two Key columns to get the needed result:

Figure 15 – Result to calculate the previous value with variable intervals (Figure by the Author)

After adding the same line Visual as before, the result is identical to the results I get from data manipulated with Power Query:

Figure 16 – Chart for 2024 with the calculation done in DAX (Figure by the Author)

Lastly, I can get rid of the intermediate Key columns by calculating them directly within a compacted and self-contained version of the Previous Value columns:

PreviousValue Compact =
VAR PreviousDate = [Previous reading date]
VAR PreviousRowKey = PreviousDate & "_" & 'MeterData_DAX'[House] & "_" & 'MeterData_DAX'[Meter]

RETURN
  CALCULATE(MIN('MeterData_DAX'[Value])
          ,REMOVEFILTERS('MeterData_DAX')
          ,FORMAT('MeterData_DAX'[Date], "YYYYMMDD") & "_" & 'MeterData_DAX'[House] & "_" & 'MeterData_DAX'[Meter] = PreviousRowKey
)

Here the results, side-by-side, which are identical:

Figure 17 – Result to calculate the previous value with the intermediate Key-columns and with the compacted (self-contained) version (Figure by the Author)

Now that we have multiple solutions, which one is the better one?

Which one is better

How should we decide which approach is the better one?

In my opinion, it comes down to the skills available.

By this, I mean the skills available in the team of those who must maintain the solution. This can be you or a client team.

Which is my preferred approach?

Do I want to prepare all the data as early as possible?
Or do I want to have the simplest solution?
Or do I have a preference for a Language?

In this case, it is about using Power Query or DAX.

I prefer to prepare my data as early as possible.

Therefore, I prefer using Power Query to prepare the data and have it ready to use without the need to add calculated columns in Power BI.

However, considering the simplicity, I must confess that the approach with the self-contained calculated column with DAX is the best solution.

But it's not that easy to exactly understand what happens and why.

Now, we can use hard facts to analyze the efficiency of the two approaches: Model statistics.

I use DAX Studio to get the Metrics (Advanced Menu and View Metrics).

I get the following information:

Figure 18 – Metrics of the Data model with both tables (Figure by the Author)

I can see that the Approach with the calculated columns in DAX uses much more memory than the Power Query approach.

But when we detract the size of the two Key columns (Above in red), we get:

930'634–332'745–332'813 = 265'076 bytes

Then, I must detract one of the two columns for the Previous Value (Above in blue): 265'076–48'248 = 207'828 bytes.

The difference in space compared to the table prepared with Power Query is marginal in this case.

But I have only 6'005 rows. It can make a huge difference when we have hundreds of thousands or even millions of rows.

I already had situations where the client wanted the solution in a specific way, as he wasn't familiar with the other approach, even though it would deliver the solution more efficiently.

Deciding on the best approach is challenging, as you might have to consider different factors.

Now that you have the information for two solutions, it's your turn to choose the right one.

References

As mentioned above, the data is self-generated without relation to the real world.

The Approach with Power Query is deduced from this Blog Post and Video:

https://gorilla.bi/power-query/get-previous-row-value/.

Here is my article about Context Transition:

What's fancy about context transition in DAX

Other solutions and approaches include using single M-expressions in Power Query. I decided to use this one because it is straightforward to implement and easy to understand what happens.

Please consider Following me and Subscribe to get E-Mails as soon as I add new content:

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: