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...
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).
