Yearly cumulative chart - problem

[Amit_B]

Hi,

I have a combo chart that displays cumulative data. In this chart, MonthID is used as the only dimension, and each year data defined as a separate measure.
The chart shows data up to the current date, based on the max selected month and a YTD flag.

These are the measures - they return the correct result:

{<Year={"$(vYear1)"}>}{<Quarter=, MonthID=>}
RangeSum(Below([TotalCost], 0, NoOfRows())) / RangeSum(Below([CountOfDates], 0, NoOfRows()))

{<Year={"$(vYear2)"}>}{<Quarter=, MonthID=>}
RangeSum(Below([TotalCost], 0, NoOfRows())) / RangeSum(Below([CountOfDates], 0, NoOfRows()))

Now I'm trying to convert this into a line chart where the dimensions are MonthID (X-axis) and Year (as a separate line), and a single measure for the same cumulative value.

This is the measure I came up with:

Aggr(

    RangeSum(Above([TotalCost], 0, NoOfRows())) / RangeSum(Above([CountOfDates], 0, NoOfRows()))

    , Year, MonthID

)

But with this setup I can't display the data the way I want.

The data is always shown only for the selected period, but I need it to show the full year for each selected year (current year until today).

I added a screenshot of the combo chart (on the right) compare to the line chart (on the left) when the user selected years 2024 and  2025, and monthID 7 and 8.

I'd appreciate any help or guidance on this.

Thanks!

***** EDIT *****

When I'm trying to create a simple measure that accumulates the number of days, I get the following result:

{<MonthID=>}  RangeSum(Above([CountOfDates], 0, NoOfRows()))

Actual:

2024: Jan - 31 days, Feb - 29 days, Mar - 31 days...
2025: Jan - 31 days, Feb - 28 days, Mar - 31 days..
Expected Result:
2024: Jan - 31, Feb - 60, Mar - 91..
2025: Jan - 31, Feb - 59, Mar - 90..

Result:
2024: Jan - 31, Feb - 29, Mar - 31..
2025: Jan - 62, Feb - 57, Mar - 62..

So, 2024 receives the values as they are, while 2025 sums each month with the same month from 2024.

[robert_mika]

Use Year as Dim and try the below

Aggr( RangeSum(Above(
Sum({ <MonthID={"<=$(=Max(MonthID))"}, Quarter=, MonthID=, Year=>
} TotalCost)
/
Sum({
<MonthID={"<=$(=Max(MonthID))"}, Quarter=, MonthID=, Year=>
} CountOfDates),
0,
RowNo() ) ), Year, MonthID)

[Amit_B]

Hi, thanks you for the help.

* Use only Year as a dimension? I need the X-axis to be by MonthID and each year should be on a separate line.
* TotalCost and CountOfDates are master items so they can't be used inside a Sum().

In any case, I tried and it didn't work for me.

[Amit_B]

I've reached a point where the measure displaying the correct data for each selected year.
However, when a specific month or quarter is selected, only that selected time range is displayed.
what should be shown is all data up to the maximum of the selected month or quarter.

For example:
If May is selected then for each selected year, all the data up to May should be displayed (January to May).
If Q2 is selected then for each selected year, all the data up to Q2 should be displayed (January to June).

This is the formula (only for one part):
{<Date={"<=$(vMaxSelectedDate)"}, Quarter=, MonthID=>}
Aggr(RangeSum(Above(CountOfDates), 0, NoOfRows())), Year, MonthID)

Thanks!