Calculate balance on last date by multiple dynamic dimensions

[alwynkfinal]

I'm trying to build a pivot table with dimensions that can be switched on and off that shows a balance of the last day of each month/year/quarter/half and aggregates correctly by all the different dimensions, however I'm having trouble using normal aggregation in a firstsorted value, my formula looks like this:

FirstSortedValue(AGGR(MAX(totalBalance),reading_date,Entity,Manufacturer,[Machine Number],[Serial Number],-AGGR(reading_date,reading_date,Entity,Manufacturer,[Machine Number],[Serial Number]))

However, the totals are showing blank like the below screenshot:



The balance shown for each month should be the last date of the month based on the field "reading_date"

Any ideas?

[JordyWegman]

Hi,

Can you give us an example QVF with some data? This makes it easier to test.

A possibility can be to use Dimensionality() if the function maybe doesn't work on aggregated levels.

Jordy

Climber

[alwynkfinal]

Hi Jordy,

Thanks for responding to my query - I really appreciate it.

I've attached the exact problem with the real data - and added a bookmark to show my selections.

You'll notice that there's a filter dropdown to select dimensions and the measure also uses variables in the aggregation to allow for dynamic aggregation.

This is because the users of this dashboards constantly move around dimensions and measures on their pivot tables to customise their report. This is also the reason why I've been struggling using dimensionality - but I could definitely be mistaken.

The end goal is simply to show by any date "dimension" the last day of the day/week/month/year as it represents the final "balance" for a machine for that specific time period.

[alwynkfinal]

Perhaps I'm trying to achieve something that isn't possible - still haven't figured out a workaround.