Forum Discussion

Sanko's avatar
Sanko
New Member
3 years ago
Solved

Gas consumption with missing readings

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 the...
Need Help
  • lukiz84's avatar
    lukiz84
    3 years ago

    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

lukiz84's avatar
lukiz84
Icon for Memorable Member rankMemorable Member
3 years ago

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