Accumulated monthly costs per year

[Amit_B]

Hi,

I am trying to present in a line chart a comparison between years of the accumulated monthly costs during each year.
I tried displaying in a table and I get the correct results. In a line chart, the results are not shown cumulatively.

My measure:
RangeSum(Above(Sum(Cost),0, NoOfRows())) // total cost
/
RangeSum(Above(Count(distinct MonthID),0, NoOfRows())) // total months

Example:
Data: 2024-01 = 20, 2024-02 = 45, 2024-03 = 30,  2023-01 = 15, 2023-02 = 25, 2023-03 = 40
So, in line chart: 2024-01 = 20 (20/1), 2024-02 = 32.5 ((20+45)/2)...
and for 2023: 2023-01 = 15, 2023-02 = 20 ((15+25/2) = 20...

Thanks.

[Qrishna · Accepted solution]

Try:

Measure = Round(Aggr(RangeSum(Above(Sum(Cost),0,RowNo())), Year, MonthID)/
Aggr(RangeSum(Above(Count(distinct MonthID),0, RowNo())), Year, MonthID), 2)

Measure = Round(Aggr(RangeSum(Above(Sum(Cost),0,RowNo()))/RangeSum(Above(Count(distinct MonthID),0, RowNo())), Year, MonthID), 2)

Both should work

Backend: create year

Data:
Num(Left(Date,4)) as Year,
Date#(Date, 'YYYY-MM') as MonthID,
Cost;
Load * Inline [
Date,Cost
2024-01,20
2024-02,45
2024-03,30
2023-01,15
2023-02,25
2023-03,40
];

[Kushal_Chawda]

@Amit_B  Instead you can try RangeAvg()

sum(aggr(RangeAvg(above(sum(Cost),0,RowNo(total))),(Year,(NUMERIC)),(MonthID,(NUMERIC)))

[Amit_B]

Thanks!