Gas consumption with missing readings

[Sanko]

Hi,

I'm quite new to power BI and I have to calculate gas consumption over a period, but there are holes in the data, like this (all gas meter has a row at the end of the month, but sometimes there is no reading):

Because of these missing data, the calculation of consumption (or at least something close to it) shold be the maximum reading minus the minimum reding of a period. I created the following measures:

max_meter = MAXA(Gas_meters[gas_meter_value])

min_meter = MINA(Gas_meters[gas_meter_value])

consumption = [max_meter]-[min_meter]

The problem is, that on gas_meter_id level the calculated consumption is correct, but higher levels (city or county summary) it's not, because it calculates the min and max value of that level, not the aggregated sum of the gas meters consumptions:

County A consumption should be 10+25+13=48 not 46, city A2 consumption should be 25+13=38 not 28.

How can I show the correct county and city values?

Thank you in advance !

Sanko

[Sanko]

Thank you, it works!

Sanko

[lukiz84 · Accepted solution]

Hi,

you could add a calculated column to your table, something like:

PreviousMeter := CALCULATE(
	MAX(Gas_meters[gas_meter_value]),
	FILTER(
		'Gas_meters',
		'Gas_meters'[date] < EARLIER('Gas_meters'[date]) &&
		'Gas_meters'[county] = EARLIER('Gas_meters'[county]) &&
		'Gas_meters'[city] = EARLIER('Gas_meters'[city]) &&
		'Gas_meters'[gas_meter_id] = EARLIER('Gas_meters'[gas_meter_id])

	)
)

And add another calculated column

GasConsumption := 
IF(
   'Gas_meters'[gas_meter_value] > 0, 
   'Gas_meters'[gas_meter_value] - 'Gas_meters'[PreviousMeter]
)

Now you can just add a measure

Gas consumption = SUM('Gas_meters'[GasConsumption])

and don't have to worry in any constellation. It just takes a little longer to refresh data (depending on the size of the original table). But I do something similar with >200k rows and it just takes seconds

BR