Dont get total in my measure in combination with table

[DutchMark]

I have a formula which I use in a table. It however doesn't show a total (and yes my show total in the layout options is on. Can someone give me a solution?

Customer - Departing Revenue Customers (12m Lookback, including last rev month) = 
//Hij neemt ook de omzet van de maand mee waarin de omzet wordt verloren, moet hier nog even naar kijken
VAR CurrentCustomerIsDeparting = [Customer - leaving customers (last 12 months)]

VAR LastRevenueDateForCustomer =
    CALCULATE(
        [Customer Last Revenue Date],
        ALLEXCEPT(
            KPI_EOL_sync_financial_transactionlines,
            KPI_EOL_sync_financial_transactionlines[Customer Code]
        )
    )

-- Bepaal eerste dag van maand 12 maanden vóór LastRevenueDate
VAR LookbackStartDate =
    DATE(YEAR(EDATE(LastRevenueDateForCustomer, -12)), MONTH(EDATE(LastRevenueDateForCustomer, -12)), 1)

-- Bepaal laatste dag van de maand van LastRevenueDate
VAR LookbackEndDate =
    EOMONTH(LastRevenueDateForCustomer, 0)

-- Bereken omzet binnen de lookbackperiode
VAR RevenueInPeriod =
    CALCULATE(
        SUM(KPI_EOL_sync_financial_transactionlines[Revenue]),
        FILTER(
            ALL('Calendar Table'),
            'Calendar Table'[Date] >= LookbackStartDate &&
            'Calendar Table'[Date] <= LookbackEndDate
        ),
        KPI_EOL_sync_financial_transactionlines[Revenue] <> 0
    )

VAR Result =
    IF (
        CurrentCustomerIsDeparting = 1 &&
        NOT(ISBLANK(LastRevenueDateForCustomer)),
        RevenueInPeriod,
        BLANK()
    )

RETURN
    IF(Result = 0, BLANK(), Result)

[v-tejrama]

Hi @DutchMark,

Thanks for reaching out to the Microsoft fabric community forum.

I was facing the same issue and tried your approach, but the total was still coming blank.
So I tried this updated measure and it worked as expected:

Customer - Departing Revenue Customers (12m Lookback) =
SUMX(
VALUES('Customer Table'[Customer Code]),
VAR CurrentCustomerIsDeparting =
LOOKUPVALUE(
'Customer - leaving customers (last 12 months)'[Customer Code],
'Customer - leaving customers (last 12 months)'[Customer Code],
'Customer Table'[Customer Code]
) <> BLANK()

VAR LastRevenueDateForCustomer =
CALCULATE(
MAX('Customer Table'[Customer Last Revenue Date]),
ALLEXCEPT(
'Customer Table',
'Customer Table'[Customer Code]
)
)

VAR LookbackStartDate =
DATE(YEAR(EDATE(LastRevenueDateForCustomer, -12)), MONTH(EDATE(LastRevenueDateForCustomer, -12)), 1)

VAR LookbackEndDate =
EOMONTH(LastRevenueDateForCustomer, 0)

VAR RevenueInPeriod =
CALCULATE(
SUM(KPI_EOL_sync_financial_transactionlines[Revenue]),
FILTER(
ALL('Calendar Table'),
'Calendar Table'[Date] >= LookbackStartDate &&
'Calendar Table'[Date] <= LookbackEndDate
),
KPI_EOL_sync_financial_transactionlines[Revenue] <> 0
)

VAR Result =
IF(
CurrentCustomerIsDeparting &&
NOT(ISBLANK(LastRevenueDateForCustomer)),
RevenueInPeriod,
BLANK()
)

RETURN IF(Result = 0, BLANK(), Result)
)

This measure uses SUMX(VALUES(Customer Code), ...) to bring in row context at the total level by looping through each customer. It ensures that your logic runs for every customer individually and then adds it up, so both the row-wise values and the total line show correctly. I tested it with sample data and confirmed that it works fine.

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @DutchMark ,

Has your issue been resolved?If the response provided by @anilelmastasi , addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

Tejaswi.

[DutchMark]

Wow, don't be so pushy after a couple of hours and I already replied...

[DutchMark]

Shows nothing anymore

In my visual table I have a customer code, customer name (Both from the transactionalble table), a customer last revenue date measure, and Period (from my calendar table) and off course this measure. 

[anilelmastasi]

Could you try this DAX:

Customer - Departing Revenue Customers (12m Lookback, including last rev month) =
SUMX(
VALUES(KPI_EOL_sync_financial_transactionlines[Customer Code]),
VAR CurrentCustomerIsDeparting =
CALCULATE(
[Customer - leaving customers (last 12 months)],
REMOVEFILTERS('Calendar Table') // Ensure this isn't filtered by period
)

VAR LastRevenueDateForCustomer =
CALCULATE(
[Customer Last Revenue Date],
ALLEXCEPT(
KPI_EOL_sync_financial_transactionlines,
KPI_EOL_sync_financial_transactionlines[Customer Code]
),
REMOVEFILTERS('Calendar Table') // Prevent the visual's period filter from interfering
)

VAR LookbackStartDate =
DATE(YEAR(EDATE(LastRevenueDateForCustomer, -12)), MONTH(EDATE(LastRevenueDateForCustomer, -12)), 1)

VAR LookbackEndDate =
EOMONTH(LastRevenueDateForCustomer, 0)

VAR RevenueInPeriod =
CALCULATE(
SUM(KPI_EOL_sync_financial_transactionlines[Revenue]),
FILTER(
ALL('Calendar Table'), // Important: include the full date range
'Calendar Table'[Date] >= LookbackStartDate &&
'Calendar Table'[Date] <= LookbackEndDate
),
KPI_EOL_sync_financial_transactionlines[Revenue] <> 0
)

VAR Result =
IF (
CurrentCustomerIsDeparting = 1 &&
NOT(ISBLANK(LastRevenueDateForCustomer)),
RevenueInPeriod,
BLANK()
)

RETURN IF(Result = 0, BLANK(), Result)
)

[DutchMark]

Nothing, I tried about every solution in ChatGPT and other AI tools, so I really need a very specific solution. 

This code does work for example. Maybe you can get something from that. My knowledge doesn't go that far. 

Customers - Revenue First 12 Months new customers =

VAR LastFullMonthEnd =

    EOMONTH(TODAY(), -1)

VAR Last12MonthsStart =

    EOMONTH(LastFullMonthEnd, -12) + 1

VAR NewCustomers =

    FILTER(

        ADDCOLUMNS(

            VALUES(KPI_EOL_sync_financial_transactionlines[Account]),

            "FirstPurchaseDate",

                CALCULATE(

                    MIN(KPI_EOL_sync_financial_transactionlines[Customer - First Purchase - calculated column])

                )

        ),

        [FirstPurchaseDate] >= Last12MonthsStart &&

        [FirstPurchaseDate] <= LastFullMonthEnd

    )

RETURN

SUMX(

    NewCustomers,

    VAR FirstPurchaseDate = [FirstPurchaseDate]

    VAR First12MonthsStart = DATE(YEAR(FirstPurchaseDate), MONTH(FirstPurchaseDate), 1)

    VAR First12MonthsEnd = EOMONTH(First12MonthsStart, 11)

    VAR RevenuePeriodEnd = MIN(First12MonthsEnd, LastFullMonthEnd)

    RETURN

        CALCULATE(

            SUM(KPI_EOL_sync_financial_transactionlines[Revenue]),

            KPI_EOL_sync_financial_transactionlines[Account] = EARLIER(KPI_EOL_sync_financial_transactionlines[Account]) &&

            KPI_EOL_sync_financial_transactionlines[Date] >= First12MonthsStart &&

            KPI_EOL_sync_financial_transactionlines[Date] <= RevenuePeriodEnd &&

            KPI_EOL_sync_financial_transactionlines[Revenue] <> 0

        )

)

[anilelmastasi]

Hello @DutchMark ,

DAX evaluates the total row without the row context of individual customers. So your variable LastRevenueDateForCustomer gets evaluated without a specific customer, which causes the rest of the logic to break or return blank. Could you try below DAX?

Customer - Departing Revenue Customers (12m Lookback, including last rev month) =
SUMX(
VALUES(KPI_EOL_sync_financial_transactionlines[Customer Code]),
VAR CurrentCustomerIsDeparting = [Customer - leaving customers (last 12 months)]
VAR LastRevenueDateForCustomer =
CALCULATE(
[Customer Last Revenue Date],
ALLEXCEPT(
KPI_EOL_sync_financial_transactionlines,
KPI_EOL_sync_financial_transactionlines[Customer Code]
)
)
VAR LookbackStartDate =
DATE(YEAR(EDATE(LastRevenueDateForCustomer, -12)), MONTH(EDATE(LastRevenueDateForCustomer, -12)), 1)
VAR LookbackEndDate =
EOMONTH(LastRevenueDateForCustomer, 0)
VAR RevenueInPeriod =
CALCULATE(
SUM(KPI_EOL_sync_financial_transactionlines[Revenue]),
FILTER(
ALL('Calendar Table'),
'Calendar Table'[Date] >= LookbackStartDate &&
'Calendar Table'[Date] <= LookbackEndDate
),
KPI_EOL_sync_financial_transactionlines[Revenue] <> 0
)
VAR Result =
IF (
CurrentCustomerIsDeparting = 1 &&
NOT(ISBLANK(LastRevenueDateForCustomer)),
RevenueInPeriod,
BLANK()
)
RETURN
IF(Result = 0, BLANK(), Result)
)

If this solved your issue, please mark it as the accepted solution. ✅