Analyzing Charging Cycles

[zudar]

Hi PowerBI friends!

I'm having quite the challenge making an analysis and, once again, I could really use your help! 🙃

Let me start that by sharing my sample PBI-file, which can be found here.

This project is about analyzing charging and usage cycles of batteries. In this example there are two batteries; 532 & 614. These two batteries are being charged with a certain energy 'amount' on certain 'Charge Dates' which are assumed to take place at a specific moment in time. This data is in a table called 'Charges'. At the same time, usages (in hours) of these batteries are recorded in a table called 'Usages'. These three tables are all there is to it. Table 'Batteries' connects both other tables through the unique 'BatteryID' column. Below, you can see these source tables with their sample data:

Now, I'm supposed to determine how much usage (in hours) has occurred between Charge Dates for a certain battery and calculate the usage rate in amount/hour for a each of those charge 'cycles'. Ultimately, I will use these usage rates (in amount/hour) in a moving average to calculate how much would probably need be charged (in amount) at the time of opening the report.

I've sketched these charging cycles and usages for both batteries below, because it is important to note that it is possible that a battery is charged while being used at the same time. In those cases, I want to cut off the usage and only take into account the parts that are literally inbetween the green 'charging moments'.

For example: for battery 532, which was used from 5:27 until 9:12 while being charged at 7:47, I only want to take into account the usage time from 7:47 until 9:12 to include in the total usage time for the charge cycle from 11-7 at 7:47 until 12-7 at 15:33.

Another more extreme example: for battery 614, which was used all the time while being charged twice, the time used between charging moments is simply the time between 12:27 and 22:55 on 12-7, which is equal to 10,46 hours. Then it got charged with an energy amount of 48 (and it is assumed that when charged, the battery is always charged 100%), so the usage rate for that cycle would be 48/10,46 = 4,59 amount/hour. 

So just to be clear, any usages that occur before the very first moment of charging (per battery) are not important in this analysis! 

And again, ultimately, I will have to calculate these usages rates for each battery and use a moving average of those usage rates to determine the amount that probably needs to be charged with at the time of opening the report. But before I get there, I would already be very happy to have that 'Usage Time' column in the 'Charges' table calculated correctly.

The only thing I've been able to do so far is create an (inaccurate) 'flag' column as below. If a charge cycle is clicked on in the left table, usages that occur between charging moments were supposed to be flagged, but it's not working the way I liked it to. It's basically useless, but that's how far I got, sadly.

Flag = CALCULATE(COUNT(Usage[Usage]),
FILTER(Charges,
Charges[Charge Date]<=MAX(Usage[To])
&& (Charges[Next Charge Date]>=MIN(Usage[From]) || Charges[Next Charge Date] = BLANK())
))

If you're still reading at this point, thank you!

And if you're able and willing to help me, that would be legendary!

[AllisonKennedy · Accepted solution]

You could try this, both as COLUMNS in charge table:

Usage Time =
VAR _extremeusage = SUMX(FILTER(Usage,Usage[Usage] = Charges[Charges] && Usage[From]<Charges[Charge Date] && Usage[To]>Charges[Next Charge Date]), DATEDIFF(Charges[Charge Date], Charges[Next Charge Date], SECOND )/3600)

VAR _normalusage = SUMX(FILTER(Usage,

Usage[Usage]=Charges[Charges] && ((Usage[From]>Charges[Charge Date] && Usage[From]< Charges[Next Charge Date]) || (Usage[To]>Charges[Charge Date] && Usage[To] < Charges[Next Charge Date]) )),
DATEDIFF(MAX(Charges[Charge Date], Usage[From]), MIN(Charges[Next Charge Date], Usage[To]),SECOND )/3600 )

RETURN
IF(_extremeusage>0, _extremeusage, _normalusage)

Usage Rate = DIVIDE(Charges[Next Amount], Charges[Usage Time])

[zudar]

@AllisonKennedy 

Amazing, thanks! I'm just trying to understand it because I'll somehow need to include those 'Charge' records where there is no 'Next Charge Date' in the 'Usage Time' calculations (maybe separately) because that'll allow me to calculate an estimated 'next amount', given a certain average 'Usage Rate' over the last (let's say) week of usage of a certain battery..

See what I'm getting to? I will try to think of something myself and possibly get back here to ask you an additional question.. if you're willing to take a look ofcourse..

Anyway, thanks again, this helps A LOT! 🙏

[zudar]

@AllisonKennedy 

So I ended up using the following calculated column for 'Usage Time':

Usage Time = 
VAR _extremeusage = SUMX(FILTER(Usage,Usage[Usage] = Charges[Charges] && Usage[From]<Charges[Charge Date] && Usage[To]>Charges[Next Charge Date]), DATEDIFF(Charges[Charge Date], Charges[Next Charge Date], SECOND )/3600)

VAR _normalusage = SUMX(FILTER(Usage,
Usage[Usage] = Charges[Charges] 
&& 
    (
        (Usage[From]>Charges[Charge Date] && 
            (Usage[From]< Charges[Next Charge Date] || Charges[Next Charge Date] = BLANK())) 
        || (Usage[To]>Charges[Charge Date] &&
            (Usage[To] < Charges[Next Charge Date]  || Charges[Next Charge Date] = BLANK()))
    )
),
DATEDIFF(MAX(Charges[Charge Date], Usage[From]), IF(Charges[Next Charge Date] = BLANK(),Usage[To],MIN(Charges[Next Charge Date], Usage[To])),SECOND )/3600 )

RETURN
IF(_extremeusage>0, _extremeusage, _normalusage)

Which seems to be working beautifully. Thank you!

[AllisonKennedy]

@zudar Glad it worked, I did wonder if you would want the ones without Next Charge Date to be included, you have understood it and come up with a great solution.

[zudar]

@AllisonKennedy Couldn't have done it without your help. Thanks again. You are the best! 🙃