how to calculate Energy consumption on daily basis

[kapilanand_29]

I have pulled a table "Query1" by direct query in powerBi with columns as TS ,Tag, Value TS is time stamp, Tag is tag id of energy meter ,Value is reading of energy meter .I need to calculate consumption at each hour, each day and each month

Please help

[v-junyant-msft]

Hi @kapilanand_29 ,

Please try these DAXs:
Create a new measure to calculate the difference in 'Value' between each hour:

Hourly Consumption = 
VAR A =
   CALCULATE(
       SUM(Query1[Value]),
       FILTER(
           ALL(Query1),
           Query1[Tag] = EARLIER(Query1[Tag]) &&
           DATEDIFF(Query1[TS], EARLIER(Query1[TS]), HOUR) = 1
       )
   ) 
RETURN
A - Query1[Value]

To calculate Daily Consumption, you can create a new column that just contains the date part of the 'TS' column.

Date Only = FORMAT(Query1[TS], "yyyy-MM-dd")

Then, create a measure to calculate the daily consumption:

Daily Consumption = 
VAR A =
CALCULATE(
       SUM(Query1[Value]),
       FILTER(
           ALL(Query1),
           Query1[Tag] = EARLIER(Query1[Tag]) &&
           Query1[Date Only] = EARLIER(Query1[Date Only])
       )
   )
VAR B =
CALCULATE(
       MIN(Query1[Value]),
       FILTER(
           ALL(Query1),
           Query1[Tag] = EARLIER(Query1[Tag]) &&
           Query1[Date Only] = EARLIER(Query1[Date Only])
       )
   )
RETURN
A - B

Similar to the daily consumption, create a measure for monthly consumption by changing the date part to month and year:

Month Only = FORMAT(Query1[TS], "yyyy-MM")

Monthly Consumption = 
VAR A =
 CALCULATE(
       SUM(Query1[Value]),
       FILTER(
           ALL(Query1),
           Query1[Tag] = EARLIER(Query1[Tag]) &&
           Query1[Month Only] = EARLIER(Query1[Month Only])
       )
   )
VAR B =
CALCULATE(
       MIN(Query1[Value]),
       FILTER(
           ALL(Query1),
           Query1[Tag] = EARLIER(Query1[Tag]) &&
           Query1[Month Only] = EARLIER(Query1[Month Only])
       )
   )
RETURN
A - B

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kapilanand_29]

Hi Dino Tao @v-junyant-msft  

while executing this DAC for monthly calculation

Month Only = FORMAT(Query1[TS], "yyyy-MM")

Following error message is coming :

A single value for column 'TS' in table 'Query1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

While calculating Hourly consumption :

Not able to use "EARLIER" function .

What may be the issue?? Is it because of my data is in direct query mode???

[v-junyant-msft]

Hi @kapilanand_29 ,

Sorry it was my mistake, all the DAXs provided before were for creating calculated columns, not for creating measures.
I tested the hourly DAX and in my case it can run:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-junyant-msft]

Hi @kapilanand_29 ,

About this information:
"This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Month Only = FORMAT(Query1[TS], "yyyy-MM")

This is a calculated column, not a measure.
On the subject of "EARLIER", allow me to test it again.

Best Regards,
Dino Tao

[ShirinArshadnia]

Hi @kapilanand_29 

Do you have a Date Dimesion?

[kapilanand_29]

yes I do have date and time dimesion in my data...