Trying to group by 2 fields and then calculate a value based on a date field

[godinbl]

The following problem concerns just one table so no Lookups to other tables are needed

I am also a complete newbie to PowerBi.

I am trying to group the data in a table called MeterReadings by "auth" and then by "meterref"

A meter reading has an auth, a readdate and can have multiple meterrefs attached to it.

I need to calculate the difference between the minimum and maximum meter reading values (called the usage) between 2 dates (the date field is called "readdate"). What I specifically need to do is calculate the usage by meterref, between the 2 dates and SUM these values, then SUM all values under that auth

The following table show a brief subset of data from the MeterReadings table

meterref auth	        readdate	meterreading
17811	1137AP6829	15/06/2023	4,958.89
17811	1137AP6829	17/06/2024	6,157.09
17812	1139AP6829	15/06/2023	16,394.65
17812	1139AP6829	17/06/2024	19,752.88
17871	1139AP6829	15/06/2023	3,161.49
17871	1139AP6829	17/06/2024	 620.53
17871	1139AP6829	16/01/2024	0

You can see from the above that auth 1137AP6829 has only 1 meterref associated with it (17811)
auth 1139AP6829 however has 2 meterrefs associated with it (17812 and 17871), one of which has a zero reading - this must be taken into account when looking up MIN and MAX values

Expected results from above are:

1137AP6829 Total Usage = 1198.20

1139AP6829 Total Usage = 6519.72

thanks for any assistance 🙂

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[Ashish_Mathur]

Hi,

With the data that you have shared, the answer should be this.  PBI file attached.

[godinbl]

Hi - with the data I have shared and explained in my earlier posts the expected results are:

1137AP6829 Total Usage = 1198.20

1139AP6829 Total Usage = 6519.72 - not $817.27

As outlined above the auth has 1 or more meterrefs attached to it so when calculating usage we need to first group on auth, then on meterref and then take the usage for each meterref under that auth and SUM the values as long as the readdate falls between those 2 dates

In the case of auth 1137AP6829 we have:

1. only 1 meterref(17811) attached to the auth and with readings between 1/6/2023 and 27/6/2024 we have 2 readings; the MAX 6157.09 and the MIN 4958.89 - the difference between these 2 values is 1198.20 so your calculation is correct for this record.

In the case of auth 1139AP6829 we have:

1. 2 meterrefs attached - 17812 with readings between 1/6/2023 and 27/6/2024 we have 2 readings; the MAX 19752.88 and the MIN 16394.65 - the difference between these 2 values is 3358.23
2. and 17871 with readings between 1/6/2023 and 27/6/2024 we have 3 readings; the MAX 3161.49 and the MIN  0 - the difference between these 2 values is 3161.49

Now, we need to SUM 3358.23 and 3161.49 to get the correct usage figure of 6519.72

I hope I have given a better explanation to my problem than in my previous posts

thanking you

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[godinbl · Accepted solution]

thank you so much for your assistance @Ashish_Mathur 

[Kedar_Pande]

@godinbl 

TotalUsageByAuth =
SUMX(
SUMMARIZE(
MeterReadings, 
MeterReadings[auth], 
MeterReadings[meterref], 
"Usage",
CALCULATE(
MAX(MeterReadings[meterreading]) - MIN(MeterReadings[meterreading])
)
),
[Usage]
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[godinbl]

thank you so much @Kedar_Pande  - one thing is missing from the calculation - I mustn't have explained it properly:

I need to also filter on readdate - so basically WHERE readdate >= MIN(readdate) and readdate <= MAX(readdate)

thanks