Ascending Order

[RichardLinderma]

I'm currently creating a couple of graphs measuring revenue against month. One of them is to display each revenue by month, and the other is to display the same except cumulatively. This is the cumulative function I have so far:

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

However, the numbers aren't turning up cumulative when put into a table of graph against the month column. Why is that, and how can I get this fixed?

[DataNinja777]

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,

[RichardLinderma]

Thanks for your answer. I've removed both the MONTH function and replaced ALL with ALLSELECTED now, unfortunately the answer is still showing up the same. And I can confirm that the months are in the correct order (though, while I don't know if this is relevant, the month column is a calculated column)