We have an issue that I'm not sure we can solve:
We have one value per building per month.
This is the current value at the time, so each building should show the latest value.
The buildings are collected into groups - these should show the sum of all buildings (latest value each).
However, I can't get it to work.
Here's what I have so far:
Sum(Aggr(FirstSortedValue([Value], -[enddate]), [Building], Year))
This leads to the result as shown above. The years are right, but the building is a SUM of the years, but it should be only the latest.
If I leave out the "Year" in the Aggr-function, the sum is right, but I only get one value per building, not one per year per building (when drilling down).
What I need is a function which returns:
The sum of the latest value value per building for all buildings in a group (with varying hierarchies), per year in this visualization.
I hope that makes sense, maybe someone more experienced knows of a solution to this.