So I have this QVD where records seize to update incrementally, the last record is dated 25/04/2022. However, I enable to generate new dates to calculate the interest accrued with the expression; RANGESUM(BELOW(SUM(DailyInterest),0,NOOFROWS())) + SUM(Interest).
My challenge is whenever I select any subsequent date, the amount defaults to 5,263.25, that's the initial amount as of 25/04/2022. Table without any date selection
Table with 27/04/2022 selection
Apparently, in the above scenario, the amount should read 5,409.45 and not 5,263.25.
Help me out here, please!
Disclaimer, there is guesswork in this answer, it might not work.
Your formula of rangesum + below (x,x,noofrows()) access the data table and sums all(noofrows) rows with date less or equal than the dimension value.
There is no set analysis in your formula; That means, when you make a selection, the other dates' data are removed from the calculation. Obviously 5263.25 (or maybe 5263.25-73.10) is an initial value, maybe sum(Interest).
One solution would be to make a set analysis that would make DailyInterest ignore your selections: RANGESUM(BELOW(SUM({1}DailyInterest),0,NOOFROWS())) + SUM(Interest).
That might cause your chart to re-show dates you don't need. In this case, you have the generic problem of "I want to see less rows, but not actually select less data". You can solve this by using "Hide zeroes" and using
IFs on every measure to make sure they are equal to zero/null for the dimension values you don't want.You could of course calculate the accrued in your data model, which would be simpler.