Consolidate LAST VALUE - FIRST VALUE at a parent level in a matrix table

[Anonymous]

Hi dear PBI experts 🙂

I'm building a Power BI report for my company and I'm struggling on this calculation:

I get from my EAM system the meter readings from my assets. Each asset belongs to a family.

Asset family	Asset number	Reading date	Reading value
A	A01	05/01/2021	100
A	A01	20/01/2021	150
A	A01	10/02/2021	180
A	A01	26/02/2021	200000
A	A01	05/03/2021	220
A	A01	25/03/2021	300
A	A02	07/01/2021	1500
A	A02	20/01/2021	1800
A	A02	06/02/2021	1850
A	A02	20/02/2021	1900
A	A02	02/03/2021	2010
A	A02	22/03/2021	2200

I would like to get the total utilization both at asset level and family level and I created the following measure

Total utilization = LOOKUPVALUE(Meter[Reading value],Meter[Reading date],MAX(Meter[Reading date])-LOOKUPVALUE(Meter[Reading value],Meter[Reading date],MIN(Meter[Reading date])

Then I display the results into a matrix table.

For example the results for "February-March" period

Asset hierarchy	Total utilization
A	-1550	KO (300-1850) expected value is 120+350=470
A01	120	OK (300-180)
A02	350	OK (2200-1850)

As expected, the results is good at the asset level but wrong at the family level. I know I should sum each single result for the family, but I don't manage to write this measure 😞

Note that due to reading mistakes, I cannot use the MAX(reading value) - MIN(reading value) to determine the utilization level, but LAST(reading value) - FIRST(reading value)

[Anonymous]

Thank you so much Jihwan Kim, your "fix" measure works great!!!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try the below measure.

The link to the sample pbix file is down below, and you can see measures inside the pbix file.

Total Utilization Feb-Mar =

VAR startvaluedate =

CALCULATE (

MIN ( Meter[Reading date] ),

ALLEXCEPT(Meter, Meter[Asset number]),

Meter[Month] <> 1

)

VAR startvalue =

CALCULATE ( MAX ( Meter[Reading value] ), Meter[Reading date] = startvaluedate )

VAR endvaluedate =

CALCULATE (

MAX ( Meter[Reading date] ),

ALLEXCEPT(Meter, Meter[Asset number]),

Meter[Month] <> 1

)

VAR endvalue =

CALCULATE ( MAX ( Meter[Reading value] ), Meter[Reading date] = endvaluedate )

RETUrn

endvalue - startvalue

Total Utilization Feb-Mar fix =

VAR summarizetable =

ADDCOLUMNS (

VALUES ( Meter[Asset number] ),

"@totalmeter", [Total Utilization Feb-Mar]

)

RETURN

SUMX ( summarizetable, [@totalmeter] )

https://www.dropbox.com/s/9vwje1flnyihgi8/seb%20abz.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.