Function for considering time active and inactive

[chrisw888]

Hello, 

Would anyone be able to assist me with my issue? I have four columns of data that looks like the below picture.

I am trying to show a table as follows using this data (note that column Power used in month) is from a different table, it does not depend on the above data.

I have this mostly set up in Power BI that I can line chart my utilisation, however this issue is causing me a problem. At the moment, i set up my site power to be the sum of the Power with an ALLEXCEPT for site, but this then returns for example 46 for site A for every month, not 22 for each month then the 24 when the previous has been decommissioned. Similarly for Site B, it returns 96 for every month, though it should show 22 for a number of days in december, then 46, then the 96. 

I am only showing my data on a monthly trend, but my utilisation % calculation would need to take into consideration the time active for each charger.

Is there anyone that could advise me on what my best solution is here?

[eliasayyy]

can you please show what the allexcept measure

[chrisw888]

Hi 

 

Sure

 

Site Power = CALCULATE(SUM(Aging[Power kwh]),ALLEXCEPT(Aging,Aging[Site]))

 

Aware its not really correct, it had worked for me for most of what I am looking to do. Its only when I got to this stage i noticed it doesnt consider these sorts of situations where the site has changed.

[eliasayyy]

ok now please explain dataset and what are you trying to achieve if its no issue like is their sny relationships , are all in in 1 table...

[chrisw888]

My ultimate goal is to get to power utilisation %. I have a secondary table that is linked via site name, and this table gives me the kwh actually used. The table above is really to calculate the total kwh available at the site each day.

Once i can get the power available each day and the power used each day, i will divide the power used by power available.

Realistically I want to show the % per site on a monthly basis, not daily, though this should be a matter of dropping out the day in date hierarchy when it comes to charting it on a line.

Bit of a head scratcher, I am still new to writing my own DAX. Long time user in excel, but trying to write this analysis into Power BI

[eliasayyy]

ok can you please share a sample of your dataset if its not an issue?

and you shared 2 tables the first one is the kw consumption per site? why is their multiple dates? for example in site B and why is there no decommission date

[chrisw888]

Sadly I dont think I have the function to add an attachment on the forums yet 😞 

[eliasayyy]

Its ok if you have one drive or dropbox or google docs, you can share a link to your data