PowerPivot calculated column - how to filter using an ordered list?

[_Adrian]

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:

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

 

[Ozeroth]

Hi Adrian,

2/3.

• I think you should have a Usage measure, and actually eliminate the 'working' columns from the final PowerPivot table (unless you are going to use them in some way beyond calculating other columns).
• In your case, I think you can boil the table down to CustomerID, MeterID, MeterReadDate, Usage.
• RolloverFlag, RolloversTD and ReadOrder could all be calculated as intermediate steps in Power Query (in order to calculate Usage) then deleted - have a play with that in Power Query.
• If you are happy to treat all usage as having occurred on the MeterReadDate, then your usage measure can be a simple SUM.
  

  Usage Sum := SUM ( METERREADS[Usage] )

1.
As an aside, if you did want to define these columns using DAX, you can simplify a bit.
For "cumulative" calculated columns, I generally use "<= EARLIER…" within CALCULATE.
For "previous" calculated columns where there is a single previous row, you can just use LOOKUPVALUE (which is good because it will return an error if multiple values are found).

[B]ReadOrder[/B]
=
CALCULATE (
    COUNTROWS ( METERREADS ),
    ALLEXCEPT ( METERREADS, METERREADS[MeterID] ),
    METERREADS[MeterReadDate] <= EARLIER ( METERREADS[MeterReadDate] )
)

[B]RolloversTD[/B]
=
CALCULATE (
    SUM ( METERREADS[RolloverFlag] ),
    ALLEXCEPT ( METERREADS, METERREADS[MeterID] ),
    METERREADS[MeterReadDate] <= EARLIER ( METERREADS[MeterReadDate] )
)

[B]MeterReadwithRollovers (unchanged from your version)[/B]
=
METERREADS[MeterRead]
    + METERREADS[RolloversTD] * 10000

[B]Usage (with LOOKUPVALUE)[/B]
=
METERREADS[MeterReadwithRollovers]
    - LOOKUPVALUE (
        METERREADS[MeterReadwithRollovers],
        METERREADS[MeterID], METERREADS[MeterID],
        METERREADS[ReadOrder], METERREADS[ReadOrder] - 1
    )


[B]Usage (with CALCULATE and MAX)[/B]
=
METERREADS[MeterReadwithRollovers]
    - CALCULATE (
        MAX ( METERREADS[MeterReadwithRollovers] ),
        METERREADS[ReadOrder]
            = EARLIER ( METERREADS[ReadOrder] ) - 1,
        ALLEXCEPT ( METERREADS, METERREADS[MeterID] )
    )

Owen

 

[_Adrian]

Hi Owen,

Firstly a massive thank you

Re 2/3, this is a great idea. The helper columns aren't needed later so once Usage can be determined, they're unnecessary. At the moment they're bloating my data model. The problem is I think I'll need knowledge of M language to do the manipulations using Power Query. For now I need to get something working, bloated or not, but your suggestion is a great solution that I'll come back to.

Re 1, thanks again - I love the LOOKUPVALUE function - hadn't seen it before and it's perfect for this.

Owe you a pint or 2 if you come to the UK!

Cheers,

Adrian