Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating kWh based on datetime and kW

Hi    I want to calculate kWh.   I have a table with time stamps and corresponding kW, and I want to convert it to kWh.     I can imagine I have to calculate some time duration in hours,...
Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Great.

 

You have the pbix file here.

 

Let me know if you need to know anything else. Thanks in advance!

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

What type of granularity do you need? Is it sufficient to calculate the kWh per day, or do you need it more detailed?  Your Calendar table only has Day level so I assume that's what we are going for?

 

By the way, CALENDARAUTO() already takes care of the MIN/MAX dates.  You can simplify the DAX.

 

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO( ),
    "Year", FORMAT( [DATE],"yyyy"),
    "Month", FORMAT ( [DATE], "MMM" ),
    "Month Number", FORMAT( [DATE], "M" ),
    "Weekday", FORMAT ( [DATE], "DDDD" ),
    "Weekday Number", WEEKDAY( [DATE], 2 ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [DATE] ) - 1 ) / 3 ) + 1,
    "Week Number", WEEKNUM([DATE],2),
    "Month Year", FORMAT([DATE],"MMM yy"),
    "Month Year Number", FORMAT( [DATE],"yyyy")+FORMAT( [DATE], "M" ),
    "date_key", FORMAT([DATE],"dd-mm-yyyy")
)
  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    Here is a first rough approach using a measure. While it technically works it has really bad performance. 

     

     

     

    kWh = 
var tt = selectedvalue(Telemetry[Timetag])
var curr = CALCULATE(sum(Telemetry[ELECTRICITY_MAP]),allselected(Telemetry),Telemetry[Timetag]=tt)
var ttprev = CALCULATE(max(Telemetry[Timetag]),allselected(Telemetry),Telemetry[Timetag]<tt)
var prev = CALCULATE(sum(Telemetry[ELECTRICITY_MAP]),allselected(Telemetry),Telemetry[Timetag]=ttprev)
return divide(curr+prev,120)*DATEDIFF(ttprev,tt,MINUTE)

     

     

     

    Thinking about this a bit more I think a measure is not really needed - a calculated column should do as well. And it might also help with the performance. What is your ultimate granularity level - is AssetID + PointName  sufficient? If yes, can you please produce a new version of the pbix that sorts the telemetry table by AssetID, PointName and Timetag , and add an index column after that?

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      6 years ago

      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.