DAX: Achieve Running Total with Multi-Level Breakdown and Overall Totals

[marie_joy]

Hi, 

I'm looking to create a DAX formula in Power BI that calculates a running total but also provides breakdowns and overall sums at different levels. Here's the scenario:

• I have tables named month_table and net_marg_product_lines.
• month_table contains a column fy_month_number for month identification (fiscal year is from July 1st until June 30th).
• net_marg_product_lines has columns for account_nb, product_code, fiscal_year, and amount.

Desired Outcome:

I want to achieve the following calculations:

1. Running Total by Account and Product: The running total should consider previous months' data for each combination of account_nb and product_code.
2. Total by Product (All Accounts): Calculate the sum of amount for all account_nb belonging to a specific product_code.
3. Total for All Products (All Accounts): Calculate the sum of amount for all account_nb and all product_code for the current context (defined by fy_month_number and fiscal_year).

Current Formula:

I've tried the following formula, but if it does correctly sum by product types, it does not by account type. I do not understand why... 

RunningTotalValue = 
VAR _CurrentMonth = MAX(month_table[cy_month_number])
VAR _CurrentFiscalYear = MAX(net_marg_product_lines[fiscal_year])
RETURN
    CALCULATE(
        SUM(net_marg_product_lines[amount]),
        FILTER(
            ALLSELECTED(month_table),
            month_table[fy_month_number] <= _CurrentMonth
        ),
        FILTER(
            ALLSELECTED(net_marg_product_lines),
            net_marg_product_lines[fiscal_year] = _CurrentFiscalYear
        )
    )

Example with two accounts and two products for July and August:

	July 2023	July 2023	July 2023	August 2023	August 2023	August 2023
Product	Income	Transport	Margin (Total)	Income	Transport	Margin (Total)
Gr-1	350000	-7000	34300	390000 (July + August)	-1400	388600
Gr-2	7800	-900	6900	10000	-1500	8500
TOTAL	357800	-7900	349900	400000	-2900	397100

[BeaBF]

@marie_joy Try with:

RunningTotal =
CALCULATE(
SUM(net_marg_product_lines[amount]),
FILTER(
ALLSELECTED(month_table),
month_table[cy_month_number] <= MAX(month_table[cy_month_number])
),
FILTER(
ALLSELECTED(net_marg_product_lines),
net_marg_product_lines[fiscal_year] = MAX(net_marg_product_lines[fiscal_year])
)
)

LastAvailableValue =
VAR CurrentMonth = MAX(month_table[cy_month_number])
VAR FiscalYear = MAX(net_marg_product_lines[fiscal_year])
VAR LastMonthWithValue =
CALCULATE(
MAX(month_table[cy_month_number]),
FILTER(
ALL(net_marg_product_lines),
net_marg_product_lines[fiscal_year] = FiscalYear &&
net_marg_product_lines[amount] <> BLANK()
)
)
VAR LastValue =
CALCULATE(
SUM(net_marg_product_lines[amount]),
FILTER(
ALL(month_table),
month_table[cy_month_number] = LastMonthWithValue
),
FILTER(
ALL(net_marg_product_lines),
net_marg_product_lines[fiscal_year] = FiscalYear
)
)
RETURN
IF(
ISBLANK(SUM(net_marg_product_lines[amount])),
LastValue,
SUM(net_marg_product_lines[amount])
)

BBF

[marie_joy]

I found a solution but I do not understand it. If I remove

        FILTER(
            ALLSELECTED(net_marg_product_lines),
            net_marg_product_lines[fiscal_year] = _CurrentFiscalYear

 from the DAX formula, I do have correct calculations...

If anyone can explain why, I will greatly appreciate!