Log In
Register
Home
May 14, 2022
Offline
Hi everybody,
I made a table for calculating the part of renewable energy used by a building for one year.
On the other side we do calculations of the energy savings of some actions and the years we would implement those actions.
Now I'm calculating this the following way:
Table 0 : Actions and year of implementing them
Table 1 - Year 0 : Actual situation - First time putting in the values of energy consumption
Table 2 - Year 1 : Table based on actual situation table 1 minus the savings of table 0 (if some actions are in that year)
Table 3 - Year 2 : Table based on table 2 minus savings of table 0 (if some actions are planned that year)
And so on.
It's was fastidious making those tabels and do the right referencing to the right cells and so on.
I do beleive their is a better way doing it but I don't see how.
I can work with Power Query and I'm starting Power Pivot if the solution bring us that way.
https://freestonebelgium-my.sh.....A?e=IUjqGT
(Tell me if the link don't work)
Cordially
Julien Vandamme
Trusted Members
October 17, 2018
Offline
I have a similar set of data and what I did is create a dashboard sheet that reads the data.
I cannot share it since it's too large and contains our own values.
But what I did was once I had a correct version of how I wanted it, I recorded a macro and replicated that took over the formulas. Once the macro is recorded you can edit it and change the variables so thant when you change the variable like Year and Yera-1 or Year -2 it takes over the current values.
Maybe it sounds complicated but it's not that difficult unless your VBA knowledge is letting you down.
Here's a sample of one of the formulas for January of the selected year:
I added an extra line in between the different calcutaltions for my own readability
Hope it makes sense
May 14, 2022
Offline
Hi, I've no VBA knowledge 🙁
If it's possible I'd like to have it all in one table so I can make PowerPivot and Charts out of it.
The other option I had is working with power queries which I duplicates and where each query is based on the previous, but that looks like it would be very slow in Excel refreshes.
I'd also like to put the "intelligence" (way of working) in Power Bi later with the possibility to change the startyears of the actions, that's why I look for a systems that could be done in Power BI.
Greetz
Trusted Members
October 17, 2018
Offline
I'll have to take a deeper look at the way you have your data.
What I did with the table where all source information is stored is add a number of extra columns with formulae to help me filter the data with the macros.
The columns A-G contain the dat read directly from the csvfile I download from the energy company, the columns H-N contain formulas that extract as the header tells you the year, month, day and the data a s date since column A is a textstring and not an actual date value, the day of the week (1=Sunday, 2=Monday, etc)
Stroom is empty except for when the month changes in that case its the max of february - the min of february
This youbcan use for any pivot table or whatever you want and can be done without VBA
May 14, 2022
Offline
Hi Hans,
I don't see where the iterative calculation is, based on the precedent ?
Cordially
Trusted Members
October 17, 2018
Offline
There is no calculation in what I showed you, the only thing I said was that if you add the formulas to your downloaded source data you can then use it at will with any pivort table, power bi or wahtever.
The data I download from my energy provider is in csv and this data I load into a table.
The columns A-G are the contents of the data i get from my provider the columns to the right and more if you make them are fourulae that extract the data from the one or more of the previous columns so that you have something to work with
May 14, 2022
Offline
Is this still in VBA ?
I'm not totally following the concept or explanation 🙁
Trusted Members
October 17, 2018
Offline
I have attached a part of the table that I reffrence for my overviews
This table I sue as my source for graphs information, fileter by year, month day or even weeks
Maybe this explains it a little bit more
1 Guest(s)
