Solved: Calculating the Interval of a Cumulative Column

johnlloyd45 · ‎01-06-2022

I have large datasets of approximately 1-minute step timeseries cumulative energy meter readings from multiple meters in multiple homes, and within Power BI I need to calculate the interval for each meter between each timestep so I can display energy consumption.

I'm relatively new to DAX and am really struggling to get this to work. I've tried following the example from this thread but am having problems with the EARLIER function. I also can't get this to work using the example data provided within that thread, so am wondering if this solution is no longer supported in the current version of Power BI?

Below is an example of the data once imported and cleaned. You'll see there are four columns of cumulative meter readings (highlighted yellow), the timestamps (highlighted orange) and the different home ID's (highlighted red).

Can anyone help me with this? It's driving me crazy!

Anonymous · ‎01-12-2022

Hi @johnlloyd45 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below to get the previous DateTime(Hour)

Pre DateTime (Hour) = 
CALCULATE (
    MAX ( 'Trial Import'[DateTime (Hour)] ),
    FILTER (
        'Trial Import',
        'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
            && 'Trial Import'[DateTime (Hour)] < EARLIER ( 'Trial Import'[DateTime (Hour)] )
    )
)

2. Create the measures as below to get the interval of Hot water, Regulated and Renewable

Hot Water Interval =
VAR _prehour =
    SELECTEDVALUE ( 'Trial Import'[Pre DateTime (Hour)] )
VAR _htusage =
    SUM ( 'Trial Import'[Hot Water Usage] )
VAR _prehtusage =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Trial Import' ),
            'Trial Import'[DateTime (Hour)] = _prehour
        ),
        'Trial Import'[Hot Water Usage]
    )
RETURN
    IF ( ISBLANK ( _prehtusage ), BLANK (), _htusage - _prehtusage )

Regulated Interval =
VAR _prehour =
    SELECTEDVALUE ( 'Trial Import'[Pre DateTime (Hour)] )
VAR _regusage =
    SUM ( 'Trial Import'[Regulated Energy Used] )
VAR _preregusage =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Trial Import' ),
            'Trial Import'[DateTime (Hour)] = _prehour
        ),
        'Trial Import'[Regulated Energy Used]
    )
RETURN
    IF ( ISBLANK ( _preregusage ), BLANK (), _regusage - _preregusage )

Renewable Interval =
VAR _prehour =
    SELECTEDVALUE ( 'Trial Import'[Pre DateTime (Hour)] )
VAR _reusage =
    SUM ( 'Trial Import'[Unregulated Energy Used] )
VAR _prerenusage =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Trial Import' ),
            'Trial Import'[DateTime (Hour)] = _prehour
        ),
        'Trial Import'[Unregulated Energy Used]
    )
RETURN
    IF ( ISBLANK ( _prerenusage ), BLANK (), _reusage - _prerenusage )

Best Regards

View solution in original post

parry2k · ‎01-06-2022

@johnlloyd45 there are many ways to do this and the only concern I will have that the performance. How large is your dataset?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

johnlloyd45 · ‎01-06-2022

The dataset wil eventually be for around 300 homes, with one meter reading being taken every 10 to 15 minutes, and it'll grow indefinitely. I can always limit the size of the dataset by archiving older readings and only keeping the last 12 months or so of data in the Power BI. Once I've got it working I'd also be looking to move away from Power BI Desktop and move the cloud hosted version which I presume would deal with large scale a lot better?

parry2k · ‎01-06-2022

@johnlloyd45 are you getting any errors? What expression did you use? It will be easier if you paste data in the table format and share the expected output.

✨ Follow us on LinkedIn and to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

johnlloyd45 · ‎01-06-2022

Sure thing. You can download an example Power BI file with a selection of the data and tables here.

https://www.dropbox.com/s/jkoweuvuw3n6649/Cumulative%20Meter%20Reading%20Example.pbix?dl=0

I've tried numerous examples from posts on this forum but haven't managed to succeed.

In this file I've filtered by a single House ID, and aggregated the data in daily meter reading values in one table, and hourly meter reading values in another table. You can see from this that the readings are cumulative and therefore do not represent the energy consumption for each day or hourly period. An example of the hourly table is below:

Home ID	DateTime (Hour)	Hot Water Usage	Regulated Energy Used	Unregulated Energy Used
188994	09/05/2021 14:00	33972	4024.567	4377.91
188994	09/05/2021 15:00	33972	4024.834	4378.033
188994	09/05/2021 16:00	33972	4025.099	4378.158
188994	09/05/2021 17:00	33972	4025.366	4378.281
188994	09/05/2021 18:00	33972	4025.631	4378.407
188994	09/05/2021 19:00	33972	4025.898	4378.531
188994	09/05/2021 20:00	33972	4026.697	4378.651

I'm looking to add some calcualted columns for each of the four meters, which is the difference between the reading at each time, and the reading at the previous time - the red columns below.

Home ID	DateTime (Hour)	Hot Water Usage	Hot Water Interval	Regulated Energy Used	Regulated Interval	Unregulated Energy Used	Renewable Interval
188994	09/05/2021 14:00	33972	0	4024.567	0	4377.91	0
188994	09/05/2021 15:00	33972	0	4024.834	0.267	4378.033	0.123
188994	09/05/2021 16:00	33972	0	4025.099	0.265	4378.158	0.125
188994	09/05/2021 17:00	33972	0	4025.366	0.267	4378.281	0.123
188994	09/05/2021 18:00	33972	0	4025.631	0.265	4378.407	0.126
188994	09/05/2021 19:00	33972	0	4025.898	0.267	4378.531	0.124
188994	09/05/2021 20:00	33972	0	4026.697	0.799	4378.651	0.12
188994	09/05/2021 21:00	33972	0	4026.994	0.297	4378.776	0.125
188994	09/05/2021 22:00	33972	0	4027.262	0.268	4378.903	0.127
188994	09/05/2021 23:00	33972	0	4027.526	0.264	4379.021	0.118
188994	10/05/2021 00:00	33972	0	4027.791	0.265	4379.144	0.123
188994	10/05/2021 01:00	33972	0	4028.579	0.788	4379.268	0.124
188994	10/05/2021 02:00	33972	0	4031.882	3.303	4379.388	0.12
188994	10/05/2021 03:00	33972	0	4032.55	0.668	4379.506	0.118

Anonymous · ‎01-12-2022

Hi @johnlloyd45 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below to get the previous DateTime(Hour)

Pre DateTime (Hour) = 
CALCULATE (
    MAX ( 'Trial Import'[DateTime (Hour)] ),
    FILTER (
        'Trial Import',
        'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
            && 'Trial Import'[DateTime (Hour)] < EARLIER ( 'Trial Import'[DateTime (Hour)] )
    )
)

2. Create the measures as below to get the interval of Hot water, Regulated and Renewable

Hot Water Interval =
VAR _prehour =
    SELECTEDVALUE ( 'Trial Import'[Pre DateTime (Hour)] )
VAR _htusage =
    SUM ( 'Trial Import'[Hot Water Usage] )
VAR _prehtusage =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Trial Import' ),
            'Trial Import'[DateTime (Hour)] = _prehour
        ),
        'Trial Import'[Hot Water Usage]
    )
RETURN
    IF ( ISBLANK ( _prehtusage ), BLANK (), _htusage - _prehtusage )

Regulated Interval =
VAR _prehour =
    SELECTEDVALUE ( 'Trial Import'[Pre DateTime (Hour)] )
VAR _regusage =
    SUM ( 'Trial Import'[Regulated Energy Used] )
VAR _preregusage =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Trial Import' ),
            'Trial Import'[DateTime (Hour)] = _prehour
        ),
        'Trial Import'[Regulated Energy Used]
    )
RETURN
    IF ( ISBLANK ( _preregusage ), BLANK (), _regusage - _preregusage )

Renewable Interval =
VAR _prehour =
    SELECTEDVALUE ( 'Trial Import'[Pre DateTime (Hour)] )
VAR _reusage =
    SUM ( 'Trial Import'[Unregulated Energy Used] )
VAR _prerenusage =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Trial Import' ),
            'Trial Import'[DateTime (Hour)] = _prehour
        ),
        'Trial Import'[Unregulated Energy Used]
    )
RETURN
    IF ( ISBLANK ( _prerenusage ), BLANK (), _reusage - _prerenusage )

Best Regards

johnlloyd45 · ‎01-17-2022

Hi @Anonymous, rather than calculating the intervals as measures, would it be possible to do them as calculated columns so that I could use aggregation to view the data at different time intervals?

Anonymous · ‎01-17-2022

Hi @johnlloyd45 ,

You can create the calculated columns as below to get them, please find the details in the attachment.

Column_Hot Water Interval = 
VAR _prehour = 'Trial Import'[Pre DateTime (Hour)]
VAR _prehtgusage =
    CALCULATE (
        SUM ( 'Trial Import'[Hot Water Usage] ),
        FILTER (
            'Trial Import',
            'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
                && 'Trial Import'[DateTime (Hour)] = _prehour
        )
    )
VAR _curhtgusage =
    CALCULATE (
        SUM ( 'Trial Import'[Hot Water Usage] ),
        FILTER (
            'Trial Import',
            'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
                && 'Trial Import'[DateTime (Hour)] = EARLIER ( 'Trial Import'[DateTime (Hour)] )
        )
    )
RETURN
    IF ( ISBLANK ( _prehtgusage ), BLANK (), _curhtgusage - _prehtgusage )

Column_Regulated Interval = 
VAR _prehour = 'Trial Import'[Pre DateTime (Hour)]
VAR _preregusage =
    CALCULATE (
        SUM ( 'Trial Import'[Regulated Energy Used] ),
        FILTER (
            'Trial Import',
            'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
                && 'Trial Import'[DateTime (Hour)] = _prehour
        )
    )
VAR _curregusage =
    CALCULATE (
        SUM ( 'Trial Import'[Regulated Energy Used] ),
        FILTER (
            'Trial Import',
            'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
                && 'Trial Import'[DateTime (Hour)] = EARLIER ( 'Trial Import'[DateTime (Hour)] )
        )
    )
RETURN
    IF ( ISBLANK ( _preregusage ), BLANK (), _curregusage - _preregusage )

Column_Renewable Interval = 
VAR _prehour = 'Trial Import'[Pre DateTime (Hour)]
VAR _prerenusage =
    CALCULATE (
        SUM ( 'Trial Import'[Unregulated Energy Used] ),
        FILTER (
            'Trial Import',
            'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
                && 'Trial Import'[DateTime (Hour)] = _prehour
        )
    )
VAR _currenusage =
    CALCULATE (
        SUM ( 'Trial Import'[Unregulated Energy Used] ),
        FILTER (
            'Trial Import',
            'Trial Import'[Home ID] = EARLIER ( 'Trial Import'[Home ID] )
                && 'Trial Import'[DateTime (Hour)] = EARLIER ( 'Trial Import'[DateTime (Hour)] )
        )
    )
RETURN
    IF ( ISBLANK ( _prerenusage ), BLANK (), _currenusage - _prerenusage )

Best Regards

johnlloyd45 · ‎01-12-2022

Thanks @Anonymous, that seems to have done exactly what I wanted, certainly for the hourly data. I'll spend some time understanding what you've done and seeing if I can also get it working for aggregations, such as daily, monthly, annual totals, etc.

You're a legend!

smpa01 · ‎01-06-2022

@johnlloyd45 if you want calculated column (opposed to measure) that means it is not affceted by filter context. If that is the case and if you are getting it from a RDBMS (e.g. MS SQL), it would be a lot performant doing it on the server side than with DAX (not that DAX can't take care of it, but performance will be concern).

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

My custom visualization projects

Plotting Live Sound: Viz1

Beautiful News:Viz1, Viz2, Viz3

Visual Capitalist: Working Hrs

Others:Easing Graph, Animated Calendar

johnlloyd45 · ‎01-06-2022

Unfortunately the incoming data is from daily CSV files dropped onto an FTP site so I have no option to do the calcualtion prior to importing into Power BI. I'm happy to do the calc either with a Measure or a Column...I just need to find a way of doing it. I can mitigate performance issues later if needed.