Calculate balance on last date by multiple dynamic dimensions
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:
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.