Power BI matrix grand totals wont add up

Question

I'm new to Power BI and facing this issue with a Power BI report grand totals of these columns won't add up. Any help is much appreciated, below are the formulas

Rolling 3 Months =
CALCULATE (
    SUM ( Deliveries[NetRevenue] ),
    DATESBETWEEN (
        Deliveries[DeliveryDate],
        MAX ( Deliveries[DeliveryDate] ) - 90,
        MAX ( Deliveries[DeliveryDate] )
    )
)

Prior 3 Months =
CALCULATE (
    SUM ( Deliveries[NetRevenue] ),
    DATESBETWEEN (
        Deliveries[DeliveryDate],
        MAX ( Deliveries[DeliveryDate] ) - 180,
        MAX ( Deliveries[DeliveryDate] ) - 90
    )
)

Screenshot of the result

Answer 1

This is likely because MAX ( Deliveries[DeliveryDate] ) is not the same for every row.

The maximum is evaluated within the local filter context, not over the entire Deliveries table (or the subset of the table that matches your filter settings).

I'm guessing you probably want to define a variable to use as your date rather than calculating it (potentially) differently for each row in your matrix. E.g.

Prior 3 Months =
VAR LastDate =
    CALCULATE ( MAX ( Deliveries[DeliveryDate] ), ALLSELECTED ( Deliveries ) )
RETURN
    CALCULATE (
        SUM ( Deliveries[NetRevenue] ),
        DATESBETWEEN ( Deliveries[DeliveryDate], LastDate - 180, LastDate - 90 )
    )

You might be able to use TODAY() instead of that LastDate calculation, depending on your particular situation.