Forum Discussion
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, so the measure should result in 1699.66 (1539.24 + 160.42).
Thanks
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
5 Replies
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).
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 Resultthe 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
