Ascending Order

Hi RichardLinderma ,

The issue with your cumulative revenue measure is that the MONTH() function extracts only the month (1 to 12), ignoring the year. This causes the calculation to incorrectly group data across different years. Additionally, using ALL('data_flight') removes all filters, including Year and Month, leading to incorrect cumulative sums when displayed in a visual. Instead, you should compare full date values to ensure proper accumulation while retaining relevant filters.

Try modifying your measure as follows:

Cumulative_Revenue =
VAR Result =
SWITCH(
    [SelectedDateType],
    "Travel",
    CALCULATE(
        SUM('data_flight'[TotalAmountGBP]),
        FILTER(
            ALLSELECTED('data_flight'), 
            'data_flight'[FirstTravelDate] <= MAX('data_flight'[FirstTravelDate])
        )
    ),
    "Ticket",
    CALCULATE(
        SUM('data_flight'[TotalAmountGBP]),
        FILTER(
            ALLSELECTED('data_flight'), 
            'data_flight'[TicketDate] <= MAX('data_flight'[TicketDate])
        )
    )
)
RETURN Result

This solution ensures that the cumulative revenue correctly sums up by comparing full date values instead of just the month number. The ALLSELECTED() function retains report filters while removing row-level filters, ensuring that only relevant selections are considered. If the months in your table or graph appear in an incorrect order, you may need to use a sorting column like:

'Calendar'[MonthSort] = YEAR([Date]) * 100 + MONTH([Date])

Then, set MonthSort as the sorting column for your month field. If the issue persists, ensure that your X-axis is using a proper Date hierarchy (Year-Month) rather than a standalone Month field.

Best regards,