topic Re: Evaluating the difference of Hourly Meter Readings with DAX in DAX Commands and Tips

Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Sat, 28 Sep 2019 13:34:01 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

kentyler — Sat, 28 Sep 2019 17:14:36 GMT

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.

Re: Evaluating the difference of Hourly Meter Readings with DAX

kentyler — Sat, 28 Sep 2019 17:15:22 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

Nathaniel_C — Sat, 28 Sep 2019 18:25:43 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

kentyler — Sat, 28 Sep 2019 19:14:44 GMT

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.

Re: Evaluating the difference of Hourly Meter Readings with DAX

Nathaniel_C — Sat, 28 Sep 2019 20:30:49 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Sat, 28 Sep 2019 22:07:03 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Sat, 28 Sep 2019 22:48:06 GMT

Yes the filter by meter and feeder is now the issue

Re: Evaluating the difference of Hourly Meter Readings with DAX

kentyler — Sat, 28 Sep 2019 23:35:22 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Sun, 29 Sep 2019 06:05:45 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

Nathaniel_C — Sun, 29 Sep 2019 14:19:02 GMT

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)

Re: Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Sun, 29 Sep 2019 15:45:18 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

Nathaniel_C — Sun, 29 Sep 2019 16:36:13 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Mon, 30 Sep 2019 09:55:37 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Mon, 30 Sep 2019 12:49:04 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

Nathaniel_C — Mon, 30 Sep 2019 14:38:37 GMT

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

Re: Evaluating the difference of Hourly Meter Readings with DAX

garbafarhan — Tue, 01 Oct 2019 07:22:10 GMT

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