Treating lastValue as an Aggregation

[MKuhns01]

I found a semi-solution I wanted to post in case anyone in the future runs into this issue.

What I opted to do was use RunningSum() to calculate the balance, and then use a date filter on a different date field as described in this Q&A :

How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?

We conceptually need to ‘hide’ those values and not filter them. To do this we will manipulate where this filter happens in the query pipeline, and by using a Table Calculation, we can make it happen all the way at the end, after the YoY calcs have already been computed. Write a calc like this: minOver(min({order date}), [{order date}]) And then put your filter on that. Should work! Before: [image] After: [image]

I can filter dates and drill down without misrepresenting the value. I have to use a parameter + control to allow users to change the group by method, but this seems to be the best option with how things are currently.

I then use a calculated field to map the parameter + control to the fields in my dataset.

My formula for Balance looks like:

runningSum({charges}, [{date} ASC], [{Grouping Parameter Calc Field}]) - runningSum({payments}, [{date} ASC], [Grouping Parameter Calc Field}])

And then I have the Grouping Parameter Calc Field in the Group By field well of the Visualization.