DAX code to compute lost revenues in PowerBI

Question

I have a table called mytable with relevant columns: customer_id, amount, year.

I would like to compute the total amount of lost revenues between 2 consecutive years.

My pseudo-code idea is:

1. find customers that spent any amount this year
2. find customers that spent any amount last year
3. find customers that spent last year but not this year using the lists at 1. and 2.
4. sum all the amounts for these customers

I tried writing the following DAX code, but I cannot compute the sum at the end, I get a syntax error.

lost revenues = 
    
    VAR current_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2020))
    
    VAR old_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2019))
    
    VAR lost_customers = EXCEPT(old_customers, current_customers)
    
    VAR computed = CALCULATETABLE(VALUES('mytable'[amount]), 'mytable'[year]=2019, 'mytable'[customer_id] IN lost_customers)
    
RETURN SUM(computed[amount])

I also tried this other version that computes a value, but it's wrong:

lost revenues = 
    
    VAR current_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2020))
    
    VAR old_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2019))
    
    VAR lost_customers = EXCEPT(old_customers, current_customers)
    
RETURN SUMX(lost_customers, CALCULATE(SUM('mytable'[amount])))

What am I doing wrong?

Edit:

Here is a minimal example of the data. As you can see, customer 3 didn't spend any amount in 2020, so the lost revenue is the sum what he spent in 2019 (197$):

Answer 1

This might be the measure you are looking for:

Lost Revenue =
VAR lastYear = SELECTEDVALUE ( mytable[year] ) - 1
VAR CustomersThisYear =  VALUES ( mytable[customer_id] )
RETURN
    CALCULATE ( SUM ( mytable[amount] ),
        mytable[year] = lastYear,
        NOT mytable[customer_id] IN CustomersThisYear
    )

This measure sums the amounts from the previous year, where the customer_id is not in this years customer_id's.

This will be the output in a table visual:

And this is the sampledata I used:

Answer 2

Assuming all the fields in your table are Whole Numbers, I've created a measure that will calculate the LostRevenue for the missing years, allocating last year as result.

I created a table DimYear to have an unique list of years, independently from the original TABLE.

Calculation: Measure

LostRevenue =
VAR SelectedCustomer =
    SELECTEDVALUE ( 'Table'[customer] )
VAR SelectedYear =
    SELECTEDVALUE ( DimYear[year] )
VAR AllCustomers =
    SUMMARIZE ( 'Table', 'Table'[customer], 'Table'[year] )
VAR AllCombinations =
    CALCULATETABLE (
        CROSSJOIN ( { SelectedCustomer }, DISTINCT ( 'Table'[year] ) ),
        REMOVEFILTERS ( 'Table'[customer] )
    )
VAR MissingYears =
    EXCEPT ( AllCombinations, AllCustomers )
VAR GetYear =
    SUMMARIZE ( MissingYears, [year] )
VAR LastYearData =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER ( ALLSELECTED ( DimYear ), [year] = SelectedYear - 1 )
    )
RETURN
    IF ( SelectedYear IN GetYear, LastYearData, BLANK () )

Relationships

New Tables created

DimYear = 
    DISTINCT('Table'[year])

Output

Table Reference: TABLE

customer	amount	year
1	10	2019
2	43	2019
3	23	2019
1	10	2019
3	76	2019
2	5	2019
1	10	2019
2	10	2019
1	55	2019
1	10	2019
3	98	2019
1	15	2020
2	40	2020
1	18	2020
1	15	2020
2	7	2020
1	6	2020
2	7	2020
1	44	2020
1	15	2020