Calculating kWh based on datetime and kW

This turned out to be much more challenging than expected.  Power BI kept running out of memory even though it is running on 32 GB.  I ended up presorting the data in SQL server (and adding the index row there)

 

 

 

 

 

SELECT 
 ROW_NUMBER() OVER(ORDER BY [Telemetry AssetID ] 
 ,[Telemetry PointName ] 
 ,[Telemetry Timetag ] asc) Row,

       [Telemetry SiteID ] [SiteID]
      ,[Telemetry AssetID ] [AssetID]
      ,[Telemetry PointName ] [PointName]
      ,[Telemetry Timetag ] [Timetag]
      ,[Telemetry Units ] [Units]
      ,[Telemetry ELECTRICITY_MAP ] [ELECTRICITY_MAP]
      ,[Telemetry StoreID ] [StoreID]
      ,[Telemetry date_key ] [date_key]
  FROM [Telemetry]

 

 

 

 

 

Then the calculated column can be written as such: (shown in two variations)

 

 

 

 

kWh = 
var i = Telemetry[Row]
var t = Telemetry[Timetag]
var ap = Telemetry[AssetID] & Telemetry[PointName]
var f = filter(Telemetry,Telemetry[Row]=i-1)
var pt = SELECTCOLUMNS(f,"pt",Telemetry[Timetag])
var pv = SELECTCOLUMNS(f,"pv",Telemetry[ELECTRICITY_MAP])
var pap= SELECTCOLUMNS(f,"pap",Telemetry[AssetID] & Telemetry[PointName])
//var pt = maxx(f,Telemetry[Timetag])
//var pv = maxx(f,Telemetry[ELECTRICITY_MAP])
//var pap = maxx(f,Telemetry[AssetID] & Telemetry[PointName])
return if (ap=pap,divide(Telemetry[ELECTRICITY_MAP]+pv,120)*DATEDIFF(pt,t,MINUTE),0)

 

 

 

 

and the calculation yields the expected result.

 

 

 

Thank you for the challenge.  I'm still baffled that Power BI struggles with such a seemingly small dataset (7.8M rows) but I guess there are a few cartesian products happening that explode the memory needs. Hope someone else can find a faster solution.