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)

[DutchMark · Accepted solution]

I fixed it myself and started from scratch. With this formula it shows which customers were lost the last 12 months.

Lost is definined as 12 full months no revevenu. So last revenue in april 2024, then 12 months not revenue will give a loss of customer in may 2025. 

It calculates the lost revenue over the lost month (so april 2024 in this example) + 11 full months before that. In this case May 2023 until April 2024.

Measure 1 (made as calculated column in   KPI_EOL_sync_financial_transactionlines):

LastRevDate per Customer =

CALCULATE(

  [Customer Last Revenue Date],

  ALLEXCEPT(

    KPI_EOL_sync_financial_transactionlines,

    KPI_EOL_sync_financial_transactionlines[Customer Code]

  )

)

Based on the measure below I first made, so after that I used this measure in the calculated column showed above. 

1.1 Customer Last Revenue Date =

IF(

    ISINSCOPE(KPI_EOL_sync_financial_transactionlines[Customer Code]),

    CALCULATE(

        MAX(KPI_EOL_sync_financial_transactionlines[Date]),

        FILTER(

            ALLEXCEPT(

                KPI_EOL_sync_financial_transactionlines,

                KPI_EOL_sync_financial_transactionlines[Customer Code]

            ),

            NOT ISBLANK(KPI_EOL_sync_financial_transactionlines[Account & GL 8])

                && KPI_EOL_sync_financial_transactionlines[Account & GL 8] <> ""

                && KPI_EOL_sync_financial_transactionlines[Account & GL 8] <> " "

                && KPI_EOL_sync_financial_transactionlines[Revenue] <> 0

        )

    ),

    BLANK()

)

Measure 2 (including a total at the bottom of a table visual):

Customer - Departing Revenue Customers (12m Lookback, including last rev month) =

// Bepaal de rapportageperiode: de laatste 12 volledige maanden

// Hij neemt de omzet in de maand dat er voor het laatste omzet is geweest + de 11 volle maanden daarvoore

VAR EindeVorigeMaand = EOMONTH(TODAY(), -1)

VAR StartRapportagePeriode = EDATE(EindeVorigeMaand, -12) + 1

RETURN

SUMX(

    // Itereer over elke unieke klant om een correct eindtotaal te garanderen

    VALUES('KPI_EOL_sync_financial_transactionlines'[Customer Code]),

    // --- Start berekening per klant ---

    VAR LaatsteOmzetDatum =

        CALCULATE(

            MAX('KPI_EOL_sync_financial_transactionlines'[LastRevDate per Customer])

        )

   

    // Bepaal de datum waarop de klant als 'verloren' wordt beschouwd

    VAR VerlorenDatum = EDATE(EOMONTH(LaatsteOmzetDatum, 0), 12) + 1

    // Controleer of de 'VerlorenDatum' binnen de gedefinieerde rapportageperiode valt

    VAR IsVerlorenInPeriode =

        LaatsteOmzetDatum <> BLANK() &&

        VerlorenDatum >= StartRapportagePeriode &&

        VerlorenDatum <= EindeVorigeMaand

    // Bereken de omzet als de klant verloren is in de periode

    VAR VerlorenOmzetBedrag =

        IF(

            IsVerlorenInPeriode,

            CALCULATE(

                SUM('KPI_EOL_sync_financial_transactionlines'[Revenue]),

                DATESINPERIOD(

                    'Calendar Table'[Date],

                    LaatsteOmzetDatum,

                    -12,

                    MONTH

                )

            )

        )

    // --- Einde berekening per klant ---

    // TOEVOEGING: Toon geen resultaat als de verloren omzet 0 is

    RETURN

        IF(

            VerlorenOmzetBedrag = 0,

            BLANK(),

            VerlorenOmzetBedrag

        )

)

Remark: In my table visual I show: Customer Code (necessary), Customer Name (not necessary), Last Rev. Date (not necessary), Month Lost (not necessary) - I  will type this measure in another message.

3. Show the measure in Chart, measure, so only one cumulative measure

Total Departing Revenue Customers (12 Months) =

CALCULATE(

    -- ====================================================================

    -- START: HIERONDER STAAT JOUW VOLLEDIGE, WERKENDE FORMULE

    -- ====================================================================

    VAR EindeVorigeMaand = EOMONTH(TODAY(), -1)

    VAR StartRapportagePeriode = EDATE(EindeVorigeMaand, -12) + 1

    RETURN

    SUMX(

        FILTER(

            SUMMARIZE(

                'KPI_EOL_sync_financial_transactionlines',

                'KPI_EOL_sync_financial_transactionlines'[Customer Code],

                "LaatsteOmzetDatum", MAX('KPI_EOL_sync_financial_transactionlines'[LastRevDate per Customer]),

                "VerlorenDatum", MAX('KPI_EOL_sync_financial_transactionlines'[Month of Customer Lost (Date)])

            ),

            [VerlorenDatum] >= StartRapportagePeriode &&

            [VerlorenDatum] <= EindeVorigeMaand

        ),

       

        VAR VerlorenOmzetBedrag =

            CALCULATE(

                SUM('KPI_EOL_sync_financial_transactionlines'[Revenue]),

                DATESINPERIOD(

                    'Calendar Table'[Date],

                    [LaatsteOmzetDatum],

                    -12,

                    MONTH

                )

            )

       

        RETURN

            IF(

                VerlorenOmzetBedrag = 0,

                BLANK(),

                VerlorenOmzetBedrag

            )

    ),

    -- ====================================================================

    -- EINDE: JOUW WERKENDE FORMULE

    -- ====================================================================

    -- DE ENIGE TOEGEVOEGDE VOORWAARDE:

    -- Negeer alle externe filters van de feitentabel en de kalendertabel

    ALL('KPI_EOL_sync_financial_transactionlines'),

    ALL('Calendar Table')

)

4. If you wanna show the numbers of leaving customers in a Chart, use this one.

Customer Chart - Total nr. of customers with 12 Months No Revenue =

-- Tel de rijen van een virtuele tabel die we hieronder bouwen

COUNTROWS(

    -- Filter de virtuele tabel van klanten

    FILTER(

        -- Stap 1: Maak een samengevatte lijst van alle klanten die in de huidige

        -- periode op de X-as vallen. Per klant leggen we de laatste omzetdatum vast.

        SUMMARIZE(

            'KPI_EOL_sync_financial_transactionlines',

            'KPI_EOL_sync_financial_transactionlines'[Customer Code],

            "LaatsteOmzetDatum", MAX('KPI_EOL_sync_financial_transactionlines'[LastRevDate per Customer])

        ),

       

        -- Stap 2: Bereken voor elke klant in deze lijst de omzet van de 12 maanden

        -- vóór hun laatste omzetdatum.

        VAR Omzet12M =

            CALCULATE(

                SUM('KPI_EOL_sync_financial_transactionlines'[Revenue]),

                DATESINPERIOD(

                    'Calendar Table'[Date],

                    [LaatsteOmzetDatum],

                    -12,

                    MONTH

                )

            )

       

        -- Stap 3: Behoud alleen de klanten waar de berekende omzet niet nul is.

        RETURN Omzet12M <> 0

    )

)

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

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