Evaluating the difference of Hourly Meter Readings with DAX

[garbafarhan]

Hi,

I have cumulative hourly meter readings (kWh) from 01/08/2018 00:00 to 31/08/2018 00:00 (https://1drv.ms/x/s!AntLmHOXlXNW0nkZHY4GZU8k3Mt5?e=bXWLuT)

Each of meter serial is unique and belongs to a certain feeder. what I want to achieve is to evaluate (using DAX column or a measure) for each meter in each feeder the hourly (kwh) from the cumulative i.e the difference between the hourly time steps readings. Thanks in advance

[Nathaniel_C]

Hi @garbafarhan , @kentyler ,

Did it! Went down a lot of rabbit holes figuring this one out. Will give you the PBIX after I get it cleaned up - lot of rabbit holes.

The crux of the matter is in Power Query, getting the sort order correctly defined and then using the insertion of an index to capture that rather than only using the index to be able to go back one value.  In addition, I created a unique identifer by concatenating the Feeder and the Meter (In case the meters are not unique across feeders else you can use the meter id) Then there are three possible returns, 0, the answer, or blank if moving on to the next identifier. So, used a couple of IFs as well. Depending on what you want to see, you could chop off rows. 

The great thing about doing it this way of course is that it is automatic with refresh!

Let me know if you have any questions and thanks for the issue.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Earlier Index = VAR earlyread = Calculate ( Max(Meter[READING]), Filter(All(Meter),Meter[Sort by time and unique ID]= EARLIER(Meter[Sort by time and unique ID])-1))
                var currentread = Meter[READING]
                var result = if (Meter[Sort by time and unique ID]=0,0, currentread - earlyread)


                
  return   IF(Meter[Unique ID]= CALCULATE(max(Meter[Unique ID]), FILTER(ALL(Meter),Meter[Sort by time and unique ID]= EARLIER(Meter[Sort by time and unique ID])-1)),result)

[Nathaniel_C · Accepted solution]

Hi @garbafarhan @kentyler ,

Here is the PBIX Difference in Meter Readings. The original data is in the first step, if you want to change it.

The important steps after you import the data in Power Query:

1. Determine the unique identifier. (See previous message)

2 Sort the order so that the unique identifier is sorted, then sort the date.  This will give you each meter side by side and the times with the earlier first. 

3. Once you have them in proper order, THEN you insert the index.  This will capture the correct order so that PBI can use it to look to the previous step to get the answer that you need.

4. Load the data to Power BI and create the calc as a new column as shown in the message above.

5. Create visuals...I put a couple that may work for you.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[garbafarhan]

Many thanks  @Nathaniel_C  it worked!. See picture below the power bi model hourly readings comparison with the excel values.  Would let you know if I have further questions.

Thank You.

Garba

[Nathaniel_C]

Hi @garbafarhan ,

So it looks like we are differing by a time period.  Could it be data? My output is on the second hour.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[garbafarhan]

---

@Nathaniel_C wrote:

Hi @garbafarhan ,

So it looks like we are differing by a time period.  Could it be data? My output is on the second hour.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

---

@Nathaniel_C  I have had a look at the excel output again, and found out that next day difference between 23:00 and 00:00 hours was not properly connected, have corrected it and now I am having an exact match between your output and mine.

Many thanks for your help

Garba

[garbafarhan]

Many thanks @Nathaniel_C , worked!, See attached comparison with excel hourly differences. Would let you know if I have further questions.

Thanks very much

Garba

[garbafarhan]

@Nathaniel_C  great, thanks for your time, would compare your pbix with the results I got in excel. As your earlier mentioned the automatic refresh with same data format for other months would be the cool thing about the power bi model compared to excel.

Thank You

[Nathaniel_C]

@garbafarhan , @kentyler ,

Here is a pic of using the index, on the table and the code.  What it doesn't do is get the previous date by meter and Feeder. 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Dif = var readnow = (Meter[READING])
        var readbefore = IF( Meter[Index] >  0, CALCULATE(MAX(Meter[READING]),FILTER(Meter, Meter[Index] = EARLIER(Meter[Index]  )-1)) ,Meter[READING])

        return readnow - readbefore

[garbafarhan]

Yes the filter by meter and feeder  is now the issue

[kentyler]

Sounds like your first step would be to add an index to the table. So you can use index-1 in your measures to give you access to the previous reading.

That should let you calculate the change from reading to reading.

[kentyler]

Then for each feeder/meter you should just be able to sum the changes.

[Nathaniel_C]

@kentyler , @garbafarhan ,

However, I believe that it is not just the previous reading, so index won't work.  It has to be filtered for feeder and meter too.

Nathaniel

[kentyler]

If you add filter and meter columns in a table or matrix it should default to grouping by filter and meter and summing the elapsed time column. If you post a sample pbx file I'll create and example for you.

[garbafarhan]

@kentyler , @Nathaniel_C  Thank you for the reply, The link to the sample pbix: pbix file

[kentyler]

I did some work with the sample file. But without the excel file its based on I can't alter the queries.

It did bring up the idea that this is all better done in excel, which has a programatic way to compare values in different rows.

you can sort the excel file by meter and by transformer and then calculate the differences.

and then load the data into power bi

It seems like you are interested in the hourly difference, so you would only need every 4th row to load into Power BI

[garbafarhan]

@kentyler , Thank for your reply. I have evaluated the hourly difference (row difference) for each meter in each feeder (see sample picture below).  Here is a link to the excel file Excel File

Thank You