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

[DutchMark]

There are other table names and

"Customer - leaving customers (last 12 months)'[Customer Code]" is a measure with me, not a table. So this doesn't work for me. 

My customer code is in 

KPI_EOL_sync_financial_transactionlines'[Customer Code]

[v-tejrama]

Hi @DutchMark ,

Thanks for pointing that out.

You're absolutely right LOOKUPVALUE doesn't work when you're trying to use it on a measure like 'Customer leaving customers (last 12 months)'[Customer Code].

I’ve updated the measure to remove the dependency on that table/measure. Instead, it now checks if the customer’s last revenue date is more than 12 months old, and if so, includes their revenue from the past 12 months (up to their last revenue month).

This way, you don’t need a separate “departing customer” table it works purely based on logic and date comparison within your existing KPI_EOL_sync_financial_transactionlines table. 

Here is the Dax :

Customer - Departing Revenue Customers (12m Lookback) =
SUMX (
VALUES ( KPI_EOL_sync_financial_transactionlines[Customer Code] ),
VAR CustomerCode = KPI_EOL_sync_financial_transactionlines[Customer Code]

VAR LastRevenueDateForCustomer =
CALCULATE (
MAX ( KPI_EOL_sync_financial_transactionlines[Customer Last Revenue Date] ),
FILTER (
ALL ( KPI_EOL_sync_financial_transactionlines ),
KPI_EOL_sync_financial_transactionlines[Customer Code] = CustomerCode
)
)
VAR CutOffDate = TODAY() - 365
VAR IsDepartingCustomer = NOT ISBLANK(LastRevenueDateForCustomer) && LastRevenueDateForCustomer <= CutOffDate

VAR LookbackStartDate =
DATE ( YEAR ( LastRevenueDateForCustomer ) - 1, MONTH ( LastRevenueDateForCustomer ), 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[Customer Code] = CustomerCode,
KPI_EOL_sync_financial_transactionlines[Revenue] <> 0
)

VAR Result =
IF (
IsDepartingCustomer,
RevenueInPeriod,
BLANK ()
)

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

Please try this updated measure in your model and let me know if it works for your scenario.

Thank you,

Tejaswi.

[DutchMark]

Thank you, but I feel this isn't going to work. Please check my orginal code. There you would have seen that:

This is a measure with me. "MAX ( KPI_EOL_sync_financial_transactionlines[Customer Last Revenue Date] ),"

But I also feel like the logic has changes in the formula. I think my cut off dates are different?

[v-tejrama]

Hi again @DutchMark ,

Thanks for your detailed reply. You're absolutely right  since Customer Last Revenue Date is a measure, we can't directly use it in row level logic like in LOOKUPVALUE or filters. I’ve now reworked the formula to handle that correctly.

Also, I’ve taken care to align the cut-off logic with your original code. This version checks if the customer’s last revenue date is older than 12 months, and if yes, it pulls their revenue from the 12 months before that just like how you had it.

Here’s the revised DAX:

Customer - Departing Revenue (Last 12m Lookback) =
VAR CutOffDate = TODAY() - 365

RETURN
SUMX (
VALUES ( 'Customer Table'[Customer Code] ),
VAR CustomerCode = 'Customer Table'[Customer Code]

VAR LastRevenueDate =
CALCULATE (
MAX ( KPI_EOL_sync_financial_transactionlines[Customer Last Revenue Date] ),
KPI_EOL_sync_financial_transactionlines[Customer Code] = CustomerCode
)

VAR IsDeparting =
NOT ISBLANK ( LastRevenueDate ) && LastRevenueDate <= CutOffDate

VAR LookbackStartDate =
DATE ( YEAR ( LastRevenueDate ) - 1, MONTH ( LastRevenueDate ), 1 )

VAR LookbackEndDate =
EOMONTH ( LastRevenueDate, 0 )

VAR RevenuePeriod =
CALCULATE (
SUM ( KPI_EOL_sync_financial_transactionlines[Revenue] ),
KPI_EOL_sync_financial_transactionlines[Customer Code] = CustomerCode,
'Calendar Table'[Date] >= LookbackStartDate,
'Calendar Table'[Date] <= LookbackEndDate
)

RETURN IF ( IsDeparting, RevenuePeriod, BLANK() )
)

This should now respect both the use of the measure and your original date logic.

Let me know if it works as expected in your model happy to help further if needed.

Thank you once again.

[DutchMark]

... MAX ( KPI_EOL_sync_financial_transactionlines[Customer Last Revenue Date] ),?? 

[v-tejrama]

Hello again  @DutchMark ,

I noticed that the “Is Departing Test” column was showing “Yes” for all customers, even those who had recent transactions like Customer C003. After investigating, we realized that the DAX logic wasn't calculating the month difference properly for each customer. So, we rewrote the measure to work customer-wise. The updated logic first uses SELECTEDVALUE to get the current customer, then fetches their last transaction date using CALCULATE and MAX, and finally calculates the number of months between that date and today using DATEDIFF. If this gap is 12 or more, it returns “Yes”; otherwise, it returns “No”.

Here's the DAX we used:

Is Departing Test =
VAR SelectedCustomer = SELECTEDVALUE(KPI_EOL_sync_financial_transactionlines[Customer Code])
VAR LastRevenueDate =
CALCULATE(
MAX(KPI_EOL_sync_financial_transactionlines[Transaction Date]),
KPI_EOL_sync_financial_transactionlines[Customer Code] = SelectedCustomer
)
VAR MonthGap = DATEDIFF(LastRevenueDate, TODAY(), MONTH)
RETURN
IF (
NOT ISBLANK(LastRevenueDate) && MonthGap >= 12,
"Yes",
"No"
)

However, there’s an important point to note here. Even if a customer’s last transaction was in June 2024 and we are now in July 2025, the DAX might still show “No”   and that’s expected. That’s because DATEDIFF counts only complete calendar months, not partial days. For example, if the last transaction was on 30-June-2024, then 12 full months would complete only on 30-June-2025. So, until that exact date is reached, the MonthGap might still be 11. This means the customer won’t be flagged as “departing” until 12 full months are completed. This is intentional, because we only want to flag a customer as “departing” if their last activity was at least 12 full months ago.

After applying this logic, we tested it using a table visual with Customer Code, Last Revenue Date, and the “Is Departing Test” result  and now it's working correctly. If needed, you can also create a simple debug measure to display the actual month gap per customer to verify that the logic is giving expected results.

Please find the attached pbix and screenshort  file for your reference.

Best Regards,
Tejaswi.
Community Support

[DutchMark]

I will have a look, but why am whole new code. My original code works perfectly, the only thing it doesn't show is a total at the bottom of the table.

also as I already wrote a couple of times. "VAR LastRevenueDate =" I already have a measure for that (which I mentioned). A new measure in this would probally be a bit heavy for my model. 

[v-tejrama]

Hi  @DutchMark  , 

Thanks for getting back!

If your original measure is already working fine and it’s just the total that’s not showing, then no need to change your entire approach. I only shared the full version to help with troubleshooting, since it was marked as the accepted solution earlier.

You can easily replace the LastRevenueDate part with your own measure to keep the performance intact. Still, I’d suggest trying out my version once it might just help fix the total issue too.

Thank you.

[DutchMark]

Sorry, but I also never asked for a total new approach. I stated my problem directly. But a couple of times my remarks about various things were just overlooked. 

I will wait till someone give a somution. It's a not a simple matter of chaning it into my measure. As the measure is the reason totals are difficult. 

[v-tejrama]

Hi @DutchMark ,

Sorry to hear the issue is still ongoing, and I truly apologize for any inconvenience caused.

I have already shared my working measure based on your requirement. But looks like you don’t want to change your current logic.

If possible, try to adjust your measure a bit using the idea I shared. I’m here and happy to help further. Will wait for your update.

Thank you.

[DutchMark]

I already tried 30 formulas via AI. I just need a working formula based on my logic which shows totals. 

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, per month)]

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]

Hello Agian @DutchMark ,

Sincere apologies from my end. I’ve tried all possible workarounds, but I understand that you’re still not satisfied with the outcome. To help me support you better, could you please share a sample version of your data (ensuring it doesn't contain any sensitive or confidential information)? Also, it would be great if you could clearly specify the exact output you’re expecting.

Thank you for your understanding!

[v-tejrama]

Hi @DutchMark ,

Sincere apologies from my end. I’ve tried all possible workarounds, but I understand that you’re still not satisfied with the outcome. To help me support you better, could you please share a sample version of your data (ensuring it doesn't contain any sensitive or confidential information)?

Also, it would be great if you could clearly specify the exact output you’re expecting.

Thank you.

[DutchMark]

My model is too extensive to break it down like this. I already told a couple of times what I expect. Think we're talking in a loop. I will just wait, maybe someone else can help with my current logic and only altering one formula, but thank you for your help

[v-tejrama]

Hi @DutchMark,

If the issue still persists on your end, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs.
You can explore the list of trusted partners here:

Power BI Partners | Microsoft Power Platform

You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.
we truly appreciate your active participation in the Microsoft Fabric Community.

Power BI Partners | Microsoft Power Platform

Find a Microsoft Power BI partner for implementation, consulting, or help getting started with Power BI to assist with your business intelligence needs.

Thank you.

[v-tejrama]

Hi @DutchMark ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tejrama]

Hi @DutchMark ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[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...