Calculate daily readings from multiple equipments

[WilliamAzevedo]

Hi.

I have to control daily readings (sometimes, readings can be skipped) from multiple equipments (water meters, more precisely) in a manner that I have a column with the date readings minus it's immediate prior (or later) date readings, for each equipment. Like this:

Equip	Date	Reading	Value
Eqp 1	10/01/25	0	0
Eqp 5	10/03/25	0	0
Eqp 1	10/02/25	30	30
Eqp 8	10/05/25	0	0
Eqp 5	10/04/25	6	6
Eqp 5	10/05/25	11	5
Eqp 1	10/06/25	42	12
Eqp 8	10/06/25	15	15
Eqp 8	10/07/25	18	3

Edit: I forgot to mention that my data sources are SharePoint lists.

Thank you very much in advance!

[danextian]

Hi @WilliamAzevedo 

Given your sample data, what is your expected result?

[WilliamAzevedo]

Hi, Dane, I'm sorry if I wasn't clear in the example. If I can help others (to help me), here's the table in a clearer formatting:

The result I expect is the column "Value" showing the subtraction of the reading in a given date from it's immediate following reading to each equipment in the column "Equip".

Edit: the table lost the formatting again, so I pasted a capture.

Thank you!

[Shahid12523]

let
Source = YourTableName,
// Ensure date column is in proper date format
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}),

// Sort by Equipment and Date
Sorted = Table.Sort(ChangedType,{{"Equip", Order.Ascending}, {"Date", Order.Ascending}}),

// Add Index per Equipment
Grouped = Table.Group(Sorted, {"Equip"}, {
{"AllRows", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}
}),
Expanded = Table.Combine(Grouped[AllRows]),

// Add Previous Reading
AddedPrev = Table.AddColumn(Expanded, "PrevReading", each try Record.Field(Expanded{[Index]-1}, "Reading") otherwise null),

// Calculate Value
AddedValue = Table.AddColumn(AddedPrev, "Value", each if [PrevReading] = null then 0 else [Reading] - [PrevReading])
in
AddedValue

[WilliamAzevedo]

Hi, @Shahid12523!

One equipment is taking the reading from the other as a start and when counts as expected in the following month:

Heres the code:

let
    Fonte = SharePoint.Tables("X", [Implementation="2.0", ViewMode="Default"]),
    #"X" = Fonte{[Id="X"]}[Items],
    #"Macromedidor Expandido" = Table.ExpandListColumn(#"X", "Macromedidor"),
    #"Macromedidor Expandido1" = Table.ExpandRecordColumn(#"Macromedidor Expandido", "Macromedidor", {"lookupValue"}, {"Macromedidor.lookupValue"}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Macromedidor Expandido1",{{"Macromedidor.lookupValue", "Macromedidor"}}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"Data", type date}, {"Leitura", Int64.Type}}),
    #"Coluna Mesclada Inserida" = Table.AddColumn(#"Tipo Alterado", "Mesclado", each Text.Combine({[Hora], [Minuto]})),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Coluna Mesclada Inserida",{{"Mesclado", "Hora da Leitura"}}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Colunas Renomeadas1",{{"Hora da Leitura", type time}}),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Tipo Alterado1",{"Macromedidor", "Macromedidor: Vazão (m³/h)", "Data", "Hora", "Minuto", "Hora da Leitura", "Leitura", "Horas Op.", "Situação do Macromedidor", "ID"}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Colunas Reordenadas",{{"Macromedidor: Vazão (m³/h)", type number}, {"Macromedidor", type text}}),
    #"Mac. e Data Classificados" = Table.Sort(#"Tipo Alterado2",{{"Macromedidor", Order.Ascending}, {"Data", Order.Ascending}}),
    #"MacIndex" = Table.Group(#"Mac. e Data Classificados", {"Macromedidor"}, {{"TodasAsLinhas", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expandido" = Table.Combine(#"MacIndex"[TodasAsLinhas]),
    #"AdLeitAnterior" = Table.AddColumn(#"Expandido", "Leitura Anterior", each try Record.Field(Expandido{[Index]-1}, "Leitura") otherwise null),
    #"CalVolume" = Table.AddColumn(#"AdLeitAnterior", "VolumePQy", each if [Leitura Anterior] = null then 0 else [Leitura] - [Leitura Anterior])
in
    CalVolume

Thank you!

[grazitti_sapna]

Hi @WilliamAzevedo,

Try below DAX

Value :=
VAR PrevReading =
CALCULATE(
MAX('Table'[Reading]),
FILTER(
'Table',
'Table'[Equip] = MAX('Table'[Equip]) &&
'Table'[Date] =
MAXX(
FILTER(
'Table',
'Table'[Equip] = MAX('Table'[Equip]) &&
'Table'[Date] < MAX('Table'[Date])
),
'Table'[Date]
)
)
)
RETURN
IF(ISBLANK(PrevReading), 0, MAX('Table'[Reading]) - PrevReading)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[WilliamAzevedo]

Hi, @grazitti_sapna!

I'm assuming it should be a measure. Here's the code with my table and column names:

Volume = 
VAR LeitAnterior =
CALCULATE(
    MAX(fMacromedidor_Leituras[Leitura]),
    FILTER(
        fMacromedidor_Leituras,
        fMacromedidor_Leituras[Macromedidor] = MAX(fMacromedidor_Leituras[Macromedidor]) &&
        fMacromedidor_Leituras[Data] =
        MAXX(
            FILTER(
                fMacromedidor_Leituras,
                fMacromedidor_Leituras[Macromedidor] = MAX(fMacromedidor_Leituras[Macromedidor]) &&
                fMacromedidor_Leituras[Data] < MAX(fMacromedidor_Leituras[Data])
            ),
            fMacromedidor_Leituras[Data]
        )
    )
)
RETURN
IF(ISBLANK(LeitAnterior), 0, MAX(fMacromedidor_Leituras[Leitura]) - LeitAnterior)

The result in all rows was 0 an it took the readings of one equipment and gave it to every equipment, including the ones that have no readings registered.

Actual data without the measure:

With the measure:

Thank you!

[grazitti_sapna · Accepted solution]

Hi @WilliamAzevedo,

You are correct is should be a Calculated columns and not measure as it should be calculated in Row context.

Try below DAX

Volume :=
VAR CurrReading = fMacromedidor_Leituras[Leitura]
VAR CurrDate = fMacromedidor_Leituras[Data]
VAR CurrEquip = fMacromedidor_Leituras[Macromedidor]

VAR PrevReading =
CALCULATE(
MAX(fMacromedidor_Leituras[Leitura]),
FILTER(
fMacromedidor_Leituras,
fMacromedidor_Leituras[Macromedidor] = CurrEquip &&
fMacromedidor_Leituras[Data] < CurrDate
)
)

RETURN
IF(ISBLANK(PrevReading), CurrReading, CurrReading - PrevReading)

Here is the sample .pbix attaches