Forum Discussion
Calculating cumulative difference between two timestamps for grouped data
Hello everyone,
this is an example of the table I have in my report. I have energy data for different smart meters installed on production machines that collect the energy consumption on a shift basis three times a day (14:00, 23:00, 05:00). The consumption values given are cumulative for the end of every shift and can be seen in the column "Cumulative Consumption". I want to have the consumption values for a given shift and smart meeter for which I need the difference between the current shift and the shift before. So the column "Consumption" would be the target values I want to calculate in Power BI. I'm new to Power Bi and DAX and honestly have no idea how to achieve this. I looked at some post, but none tackle quite the same problem as mine since I'm not looking at differences between days but between shifts.
Thanks a lot in advance!
Trisulara , Seem like I missed earlier at one place
new column =
var _max = maxx(filter(Table, [smart Meter] = earlier([smart Meter]) && [datetime] <earlier([Datetime])),[Datetime])
return
[Cumulative Consumption] - maxx(filter(Table, [smart Meter] = earlier([smart Meter]) && [datetime] =_max),[Cumulative Consumption])
4 Replies
Trisulara , Try a new column like
new column =
var _max = maxx(filter(Table, [smart Meter] = earlier([smart Meter]) && [datetime] <[Datetime]),[Datetime])
return
[Cumulative Consumption] - maxx(filter(Table, [smart Meter] = earlier([smart Meter]) && [datetime] =_max),[Cumulative Consumption])Thanks for the quick reply, but this does not seem to work, since it returns exactly the same values as in the "Cumulative Consumption" column 🤔
Trisulara , Seem like I missed earlier at one place
new column =
var _max = maxx(filter(Table, [smart Meter] = earlier([smart Meter]) && [datetime] <earlier([Datetime])),[Datetime])
return
[Cumulative Consumption] - maxx(filter(Table, [smart Meter] = earlier([smart Meter]) && [datetime] =_max),[Cumulative Consumption])
