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]

@v-tejrama 

So I've added a calculated column bases on my measure. 

LastRevDate per Customer =

CALCULATE(

  [Customer Last Revenue Date],

  ALLEXCEPT(

    KPI_EOL_sync_financial_transactionlines,

    KPI_EOL_sync_financial_transactionlines[Customer Code]

  )

)

Would that help for this formula to get totals (who now uses the measure in stead of calculated column). In another formula this also helps to get a total. This is my code what needs alternating. Change measure for calculated column. 

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

//Hij neemt de omzet van de maand waarin de klant vertrekt + de 12 volle maanden daarvoor

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]

Hi @DutchMark ,

Thank you for your follow-up. The behavior you observed is expected: when using the [Customer Last Revenue Date] measure, it evaluates within the current filter context, so at the total level it cannot aggregate, which results in missing totals.

By creating a LastRevDate per Customer calculated column, you have effectively set the last revenue date at the customer level. This eliminates ambiguity at the total level, and using this column in your measure will produce accurate results.

Below is your revised measure utilizing the column instead of the measure:

Customer - Departing Revenue Customers (12m Lookback, including last rev month) =
VAR CurrentCustomerIsDeparting =
    [Customer - Leaving customers (last 12 months, per month)]

VAR LastRevenueDateForCustomer =
    MAX ( KPI_EOL_sync_financial_transactionlines[LastRevDate per Customer] )

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 )

With this approach:

Row-level results remain unchanged.

At the total level, aggregating with MAX on your calculated column ensures each customer's last revenue date is accurately considered, resulting in correct totals.

[DutchMark]

The erros lies in this part:

The total result is always blank due to the formula build up. It even returns nothing. With all due respect but maybe it's best if someone else would reply. We are not getting to a solution. I'm now working also on Reddit on this problem.

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

[v-tejrama]

Hi  @DutchMark ,

Thank you for your continued efforts in testing the approaches we’ve provided. As some time has passed and multiple solutions have been attempted without achieving the desired outcome, we kindly suggest raising a support ticket with the Microsoft product team. They will be able to investigate the matter further and provide more in-depth assistance to help resolve the issue.

You can raise a support ticket using the following link: Submit a product support request

Additionally, if you do find a resolution through the support channel, we would sincerely appreciate it if you could share your findings with the community. Your insights may help other members facing similar challenges.

Thank you for your patience and understanding. Please continue to engage with the Fabric Community for any further questions or support needs.

Best Regards,

Tejaswi.

Community Support

[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 · Accepted solution]

5. If you want to show one cumulative number for number of customers lost.

Total Nr of Departing Customers (12 Months) =

// Berekend cumulatief aantal vertrekkende klanten van de laatste 12 volledige maand.

// Negeert de filters die op de pagina gelden

CALCULATE(

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

    -- START: HIERONDER STAAT JOUW VOLLEDIGE, WERKENDE FORMULE

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

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

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

    RETURN

    COUNTROWS(

        FILTER(

            SUMMARIZE(

                FILTER(

                    'KPI_EOL_sync_financial_transactionlines',

                    'KPI_EOL_sync_financial_transactionlines'[Month of Customer Lost (Date)] >= StartRapportagePeriode &&

                    'KPI_EOL_sync_financial_transactionlines'[Month of Customer Lost (Date)] <= EindeVorigeMaand

                ),

                'KPI_EOL_sync_financial_transactionlines'[Customer Code],

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

            ),

            VAR VerlorenOmzetBedrag =

                CALCULATE(

                    SUM('KPI_EOL_sync_financial_transactionlines'[Revenue]),

                    DATESINPERIOD(

                        'Calendar Table'[Date],

                        [LaatsteOmzetDatum],

                        -12,

                        MONTH

                    )

                )

            RETURN

                VerlorenOmzetBedrag <> 0

        )

    ),

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

    -- 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')

)

By the way "raising a ticket with the Microsoft product team' doesn't work. They don't reply.

The code for month lost is (for period), used as calculated column in KPI_EOL_sync_financial_transactionlines : As I said before I show this in my table visual, it's not necessary, but provides some insight.

Month of Customer Lost (Period) =

// Definieer de benodigde variabelen

VAR LaatsteOmzetDatum = 'KPI_EOL_sync_financial_transactionlines'[LastRevDate per Customer]

VAR HuidigeOmzet = 'KPI_EOL_sync_financial_transactionlines'[Revenue]

VAR VerlorenMaandDatum = EDATE(LaatsteOmzetDatum, 13)

// Bepaal de einddatum van de vorige volledige maand

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

// De voorwaarden

VAR HeeftOmzet = NOT(ISBLANK(HuidigeOmzet))

VAR NietInToekomstigeMaand = VerlorenMaandDatum <= EindeVorigeMaand

// Geef alleen een resultaat terug als aan beide voorwaarden is voldaan

RETURN

    IF(

        HeeftOmzet && NietInToekomstigeMaand,

        FORMAT(VerlorenMaandDatum, "MMM-yy", "en-US"),

        BLANK()

    )

The code of month lost (for date) is (used as calculated column in KPI_EOL_sync_financial_transactionlines).

Month of Customer Lost (Date) =

// Definieer de benodigde variabelen

VAR LaatsteOmzetDatum = 'KPI_EOL_sync_financial_transactionlines'[LastRevDate per Customer]

VAR HuidigeOmzet = 'KPI_EOL_sync_financial_transactionlines'[Revenue]

VAR VerlorenMaandDatum = EDATE(LaatsteOmzetDatum, 13)

// Bepaal de einddatum van de vorige volledige maand

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

// De voorwaarden

VAR HeeftOmzet = NOT(ISBLANK(HuidigeOmzet))

VAR NietInToekomstigeMaand = VerlorenMaandDatum <= EindeVorigeMaand

// Geef alleen een resultaat terug als aan beide voorwaarden is voldaan

RETURN

    IF(

        HeeftOmzet && NietInToekomstigeMaand,

        // Geef de EERSTE DAG van de verloren maand terug

        DATE(YEAR(VerlorenMaandDatum), MONTH(VerlorenMaandDatum), 1),

        BLANK()

    )

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