Forum Discussion
Solved
Tabular measure.
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...
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
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
the measure should result in 1699.66 (1539.24 + 160.42).
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