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