Hi,
I have data relating to meter readings that use meters with a 4-digit display, such as a domestic water meter or a gas meter. I’m trying to calculate usage by using successive meter readings, for example meter reading in Jan is 5000 and in July is 7000 shows usage of 2000 units over 6 months.
When the meter reaches 9999 it rolls over to 0000 and I have a flag which shows a meter rollover. Over a long period and with high usage a meter can rollover more than once, but I’m assuming it doesn’t rollover twice between consecutive meter reads.
My METERREADS table has columns CustomerID, MeterID, MeterReadDate, MeterRead and RolloverFlag, and I’ve so far added calculated columns for the number of rollovers to date, the meter reading with the rollovers and an index of the date order of MeterReadDate:
<tbody>
</tbody>
I have also set up a Calendar and linked the MeterReadDate with the Calendar[Date].
What I want to do is add another column which works out the usage since the last read, so it uses the MeterRead in that row and subtracts the MeterRead from ReadOrder minus 1.
I’m new to PowerPivot and taking some time to adjust to it. Measures might be better than calculated columns – I’m happy to be converted if there’s a different solution.
For my calculated columns I’ve used these:
RolloversTD = CALCULATE(SUM(METERREADS[RolloverFlag]),ALLEXCEPT(METERREADS, METERREADS[MeterId]),DATESBETWEEN('Calendar'[Date],FIRSTDATE(ALL('Calendar'[Date])),LASTDATE('Calendar'[Date])))
MeterReadwithRollovers = [MeterRead]+[RolloversTD]*10000
ReadOrder = CALCULATE(COUNTROWS(METERREADS),ALLEXCEPT(METERREADS,METERREADS[MeterId]),DATESBETWEEN('Calendar'[Date],FIRSTDATE(ALL('Calendar'[Date])),LASTDATE('Calendar'[Date])))
So, the bit where I’m currently stuck is to make the following ‘dynamic’ by picking up the previous read using the ReadOrder (and not static 1) for the relevant MeterID (and not the static “Meter1”):
Usage = [MeterReadwithRollovers] - CALCULATE(SUM(METERREADS[MeterRead]),FILTER(METERS,_METERS[MeterId]="Meter1"),FILTER(METERREADS,METERREADS[ReadOrder]=1))
(This uses a lookup table which defines the Meters.)
So, I guess my questions are:
1. How do I make the Usage calculation ‘dynamic’?
2. Am I OK using calculated columns or should I be using measures?
3. If yes, how do I re-write the expressions as measures?
Any help with this is much appreciated including any tips / suggestions to neaten up what I have so far.
Cheers,
Adrian
I have data relating to meter readings that use meters with a 4-digit display, such as a domestic water meter or a gas meter. I’m trying to calculate usage by using successive meter readings, for example meter reading in Jan is 5000 and in July is 7000 shows usage of 2000 units over 6 months.
When the meter reaches 9999 it rolls over to 0000 and I have a flag which shows a meter rollover. Over a long period and with high usage a meter can rollover more than once, but I’m assuming it doesn’t rollover twice between consecutive meter reads.
My METERREADS table has columns CustomerID, MeterID, MeterReadDate, MeterRead and RolloverFlag, and I’ve so far added calculated columns for the number of rollovers to date, the meter reading with the rollovers and an index of the date order of MeterReadDate:
CustomerID | MeterID | MeterReadDate | MeterRead | RolloverFlag | RolloversTD | MeterReadwithRollovers | ReadOrder |
Customer1 | Meter1 | 22/04/2013 | 719 | 0 | 0 | 719 | 1 |
Customer1 | Meter1 | 30/04/2013 | 3692 | 0 | 0 | 3692 | 2 |
Customer1 | Meter1 | 15/05/2013 | 9893 | 0 | 0 | 9893 | 3 |
Customer1 | Meter1 | 31/05/2013 | 6242 | 1 | 1 | 16242 | 4 |
Customer1 | Meter1 | 30/06/2013 | 8810 | 0 | 1 | 18810 | 5 |
Customer1 | Meter1 | 04/07/2013 | 0 | 1 | 2 | 20000 | 6 |
Customer1 | Meter1 | 26/07/2013 | 9999 | 0 | 2 | 29999 | 7 |
Customer1 | Meter1 | 31/07/2013 | 897 | 1 | 3 | 30897 | 8 |
<tbody>
</tbody>
I have also set up a Calendar and linked the MeterReadDate with the Calendar[Date].
What I want to do is add another column which works out the usage since the last read, so it uses the MeterRead in that row and subtracts the MeterRead from ReadOrder minus 1.
I’m new to PowerPivot and taking some time to adjust to it. Measures might be better than calculated columns – I’m happy to be converted if there’s a different solution.
For my calculated columns I’ve used these:
RolloversTD = CALCULATE(SUM(METERREADS[RolloverFlag]),ALLEXCEPT(METERREADS, METERREADS[MeterId]),DATESBETWEEN('Calendar'[Date],FIRSTDATE(ALL('Calendar'[Date])),LASTDATE('Calendar'[Date])))
MeterReadwithRollovers = [MeterRead]+[RolloversTD]*10000
ReadOrder = CALCULATE(COUNTROWS(METERREADS),ALLEXCEPT(METERREADS,METERREADS[MeterId]),DATESBETWEEN('Calendar'[Date],FIRSTDATE(ALL('Calendar'[Date])),LASTDATE('Calendar'[Date])))
So, the bit where I’m currently stuck is to make the following ‘dynamic’ by picking up the previous read using the ReadOrder (and not static 1) for the relevant MeterID (and not the static “Meter1”):
Usage = [MeterReadwithRollovers] - CALCULATE(SUM(METERREADS[MeterRead]),FILTER(METERS,_METERS[MeterId]="Meter1"),FILTER(METERREADS,METERREADS[ReadOrder]=1))
(This uses a lookup table which defines the Meters.)
So, I guess my questions are:
1. How do I make the Usage calculation ‘dynamic’?
2. Am I OK using calculated columns or should I be using measures?
3. If yes, how do I re-write the expressions as measures?
Any help with this is much appreciated including any tips / suggestions to neaten up what I have so far.
Cheers,
Adrian