column grand total not summing up properly

[arnie]

* My dax below is giving me correct row subtotal and row grand totals. BUT it is giving me incorrect column grandtotals.

* The grand totals should be the sum of the [Tonnes Actual] component up to current monthyear + [Tonnes Budget] component for future monthyear values from current monthyear value.

* Instead it is giving me only the [Tonnes Budget] values across all dates. 

Please help.

_Calc. Budget =

VAR CurrentMonthYear = VALUE(FORMAT(TODAY(), "YYYYMM"))

VAR ColumnDate = MAX('Date'[Date])

VAR ColumnMonthYear =

    IF(

        ISBLANK(ColumnDate),

        BLANK(),

        VALUE(FORMAT(ColumnDate, "YYYYMM"))

    )

VAR IsGrandTotal = ISBLANK(ColumnDate)

VAR ActualValue =

    IF(

        ColumnMonthYear <= CurrentMonthYear,

        [Tonnes Actual],

        BLANK()

    )

VAR BudgetValue =

    IF(

        ColumnMonthYear > CurrentMonthYear,

        [Tonnes Budget],

        BLANK()

    )

VAR ActualTotal =

    CALCULATE(

        [Tonnes Actual],

        FILTER(

            ALL('Date'),

            VALUE('Date'[Month Year]) <= CurrentMonthYear

        )

    )

VAR BudgetTotal =

    CALCULATE(

        [Tonnes Budget],

        FILTER(

            ALL('Date'),

            VALUE('Date'[Month Year]) > CurrentMonthYear

        )

    )

RETURN

IF(

    IsGrandTotal,

    // For Grand Total: Sum all Actual and Budget values across all relevant dates

    ActualTotal + BudgetTotal,

    // For regular rows, return Actual or Budget based on the month

    ActualValue + BudgetValue

)

[arnie]

[arnie · Accepted solution]

I have the solution DAX below just to give closure to this thread. Thank you for your efforts.

_Calc. Budget =

VAR CurrentDate = TODAY()

VAR CurrentMonthYear = FORMAT(CurrentDate, "YYYYMM")

VAR StartMonthYear = "202407"

-- Check the level of the calculation

VAR IsRowLevel =

    ISINSCOPE(SalesData[Product Type]) ||

    ISINSCOPE(SalesData[Order Group]) ||

    ISINSCOPE(SalesData[Sales Class Alt.]) ||

    ISINSCOPE(SalesData[Region]) ||

    ISINSCOPE(SalesData[Market])

VAR IsColumnLevel = ISINSCOPE('Date'[Year-Month Code])

-- Calculate actual and budget values for the current Year-Month Code

VAR ColumnDate = MAX('Date'[Year-Month Code])

VAR ActualTonnesForMonth =

    CALCULATE(

        [Tonnes Actual],

        'Date'[Year-Month Code] = ColumnDate,

        'Date'[Year-Month Code] >= StartMonthYear,

        'Date'[Year-Month Code] <= CurrentMonthYear

    )

VAR BudgetTonnesForMonth =

    CALCULATE(

        [Tonnes Budget],

        'Date'[Year-Month Code] = ColumnDate,

        'Date'[Year-Month Code] > CurrentMonthYear

    )

-- Calculate actual and budget values for all Year-Month Codes in the row's scope

VAR ActualTonnesForRow =

    CALCULATE(

        [Tonnes Actual],

        'Date'[Year-Month Code] >= StartMonthYear,

        'Date'[Year-Month Code] <= CurrentMonthYear,

        REMOVEFILTERS('Date'[Year-Month Code])

    )

VAR BudgetTonnesForRow =

    CALCULATE(

        [Tonnes Budget],

        'Date'[Year-Month Code] > CurrentMonthYear,

        REMOVEFILTERS('Date'[Year-Month Code])

    )

-- Total values for columns or rows

VAR TotalForMonth = ActualTonnesForMonth + BudgetTonnesForMonth

VAR TotalForRow = ActualTonnesForRow + BudgetTonnesForRow

-- Grand Total Calculation

VAR GrandTotal =

    CALCULATE(

        ActualTonnesForRow + BudgetTonnesForRow,

        REMOVEFILTERS('Date'[Year-Month Code]),  -- Remove filters for Year-Month Code

        REMOVEFILTERS(SalesData)  -- Remove filters for SalesData

    )

-- Determine the value based on context

RETURN

IF(

    IsRowLevel && IsColumnLevel,

    -- At the intersection of row and column (specific Year-Month Code and row level)

    IF(

        ColumnDate <= CurrentMonthYear && ColumnDate >= StartMonthYear,

        [Tonnes Actual],

        IF(

            ColumnDate > CurrentMonthYear,

            [Tonnes Budget],

            BLANK()

        )

    ),

    IF(

        IsRowLevel,

        -- Subtotal for rows (aggregate across all months for the row's scope)

        TotalForRow,

        IF(

            IsColumnLevel,

            -- Subtotal for columns (aggregate across all rows for the column's scope)

            TotalForMonth,

            -- Grand total (bottom-right cell)

            GrandTotal

        )

    )

)

[Bibiano_Geraldo]

HI @arnie ,
Please Try the following DAX code:

_Calc. Budget =
VAR CurrentMonthYear = VALUE(FORMAT(TODAY(), "YYYYMM"))
VAR ColumnDate = MAX('Date'[Date])
VAR ColumnMonthYear =
    IF(
        ISBLANK(ColumnDate),
        BLANK(),
        VALUE(FORMAT(ColumnDate, "YYYYMM"))
    )

VAR IsGrandTotal = ISINSCOPE('Date'[Month Year]) = FALSE

VAR ActualValue =
    IF(
        ColumnMonthYear <= CurrentMonthYear,
        [Tonnes Actual],
        BLANK()
    )

VAR BudgetValue =
    IF(
        ColumnMonthYear > CurrentMonthYear,
        [Tonnes Budget],
        BLANK()
    )

// Correcting the Actual Total to sum up until the current month-year
VAR ActualTotal =
    CALCULATE(
        [Tonnes Actual],
        REMOVEFILTERS('Date'),
        'Date'[Month Year] <= CurrentMonthYear
    )

// Correcting the Budget Total to sum from future months beyond the current month-year
VAR BudgetTotal =
    CALCULATE(
        [Tonnes Budget],
        REMOVEFILTERS('Date'),
        'Date'[Month Year] > CurrentMonthYear
    )

RETURN
IF(
    IsGrandTotal,
    // For Grand Total: Combine the corrected Actual and Budget totals
    ActualTotal + BudgetTotal,
    // For individual rows, show the Actual or Budget based on the current month-year
    ActualValue + BudgetValue
)

[arnie]

Hi @Bibiano_Geraldo , sorry that didn't work, thank you for trying.

[Kedar_Pande]

@arnie 

Modified version of your DAX measure:

_Calc. Budget =
VAR CurrentMonthYear = VALUE(FORMAT(TODAY(), "YYYYMM"))
VAR ColumnDate = MAX('Date'[Date])
VAR ColumnMonthYear =
IF(
ISBLANK(ColumnDate),
BLANK(),
VALUE(FORMAT(ColumnDate, "YYYYMM"))
)

VAR IsGrandTotal = ISINSCOPE('Date'[Date]) = FALSE

VAR ActualValue =
IF(
ColumnMonthYear <= CurrentMonthYear,
[Tonnes Actual],
BLANK()
)

VAR BudgetValue =
IF(
ColumnMonthYear > CurrentMonthYear,
[Tonnes Budget],
BLANK()
)

VAR ActualTotal =
CALCULATE(
[Tonnes Actual],
FILTER(
ALL('Date'),
VALUE('Date'[Month Year]) <= CurrentMonthYear
)
)

VAR BudgetTotal =
CALCULATE(
[Tonnes Budget],
FILTER(
ALL('Date'),
VALUE('Date'[Month Year]) > CurrentMonthYear
)
)

RETURN
IF(
IsGrandTotal,
ActualTotal + BudgetTotal,
ActualValue + BudgetValue
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[arnie]

Sorry @Kedar_Pande , that gave the same result as @Bibiano_Geraldo  did.

[arnie]

* Hi Kedar, please see my screenshots below. 

* As you can see, Tonnes + Budget does add up correctly for corresponding date, but the column grand total (3rd table) is the same as the total for budget (2nd table). I need the highlighted totals for Actuals to ADD up with the budget values from Dec 24 onwards to give grand total of 364,007.

* Your solution is not adding up also, but thanks for trying.