Build table with cumulative sum within date subsets

[aguitatierra]

I've prepared a simplification of my work in Excel to make it easier to explain. My initial dataframe is the one with yellow background and I'd like to be able to select a date in the Dashboard, for instance, 15/05/2023 and that the table with grey background updates as shown in the image. It shows the cumulative sum of DailySpending within different registry subsets based on dates (year, month, day)

Until now I've tried with Set Analyisis, Aggr() and RangeSum(), but with no success. For the column MonthlySpending_Real my trials for the expression have been in the line of:

Aggr( Rangesum(Above(Sum({$<Type={'Real'}>} [DailySpending], 0, 12))), (Year, (NUMERIC, Ascending)), (Month, (NUMERIC, Ascending)))

Many thanks for any help! 🙂

[Gabbar]

As i can observe You have two Fields Monthly Spending Cums, and Yearly SpendingCums, If yes use these,
Try these expression with group as Dimension:-
Sum({< Type = {'Real'}>}DailySpending) for Daily Spending_real,
Sum({<Type = {'Real'},Date=,Month={"$(=Month(Date))"},Year={"$(=Year(Date))"}>}MonthlySpending_Cum),
Sum({<Type = {'Real'},Date=,Month=,Year={"$(=Year(Date))"}>}YearlySpending_Cum)

if not creating them in load Script will be a better method for that try this:
A:
Load * from Source;
noconcatenate
B:
load *, if (Group = previous(Group) and Type = previous(Type), DailySpending + peek(MonthlySpending),DailySpending) as MonthlySpending resident A 
order by group Asc, Type Asc, Date Asc;

Drop Table A;
noconcatenate
C:
load *, if (Group = previous(Group) and Type = previous(Type), MonthlySpending + peek(YearlySpending),MonthlySpending) as YearlySpending resident B
order by group Asc, Type Asc, Date Asc;

Drop Table B;

[aguitatierra]

Hi Gabbar, thanks a lot for your reply. As you suggested the way forward for my case was to modify the data before loading it.

[Gabbar]

If the issue is resolved, please mark the question as marked by accepting a comment as a solution.