Tabular measure.

[GFire]

Hi,

I need help with this measure:

Create a measure that sums the total cumulative sales of all customers who had no sales last year.

For this table, customers A and C had no sales last year, so the measure should result in 1699.66 (1539.24 + 160.42).

Thanks

[johnt75]

You can try

Cumulative Sales No Sales Last Year =
VAR AllCustomers =
    CALCULATETABLE ( DISTINCT ( 'Table'[Customer] ), REMOVEFILTERS () )
VAR PrevYear =
    YEAR ( TODAY () ) - 1
VAR CustomersWithSalesLastYear =
    CALCULATETABLE ( DISTINCT ( 'Table'[Customer] ), 'Table'[Year] = PrevYear )
VAR CustomersNoSalesLastYear =
    EXCEPT ( AllCustomers, CustomersWithSalesLastYear )
VAR Result =
    CALCULATE ( SUM ( 'Table'[Sales] ), CustomersNoSalesLastYear )
RETURN
    Result

[GFire]

the measure should result in 1699.66 (1539.24 + 160.42).

[johnt75 · Accepted solution]

You need to also filter out the rows where sales is 0

Cumulative Sales No Sales Last Year] =
VAR AllCustomers =
    CALCULATETABLE ( DISTINCT ( 'Table'[Customer] ), REMOVEFILTERS () )
VAR PrevYear =
    YEAR ( TODAY () ) - 1
VAR CustomersWithSalesLastYear =
    CALCULATETABLE ( DISTINCT ( 'Table'[Customer] ), 'Table'[Year] = PrevYear, 'Table'[Sales] > 0 )
VAR CustomersNoSalesLastYear =
    EXCEPT ( AllCustomers, CustomersWithSalesLastYear )
VAR Result =
    CALCULATE ( SUM ( 'Table'[Sales] ), CustomersNoSalesLastYear )
RETURN
    Result

[bhanu_gautam]

@GFire Create a calculated column to check if a customer had sales last year:

DAX
HadSalesLastYear =
VAR CurrentYear = 'Table'[Year]
VAR Customer = 'Table'[Customer]
VAR SalesLastYear =
CALCULATE(
SUM('Table'[totalSales]),
FILTER(
'Table',
'Table'[Customer] = Customer &&
'Table'[Year] = CurrentYear - 1
)
)
RETURN
IF(SalesLastYear > 0, 1, 0)

Create a measure to sum the total sales for customers who had no sales last year:

DAX
TotalCumulativeSalesNoSalesLastYear =
CALCULATE(
SUM('Table'[totalSales]),
FILTER(
'Table',
'Table'[HadSalesLastYear] = 0
)
)

[GFire]

the measure should result in 1699.66 (1539.24 + 160.42).