Reputation: 6960
How to use SUMMARIZECOLUMNS and retain results for all values of specific column Date[Date] (Calendar)?
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Date],
'Sales'[CustomerKey],
"__MaxDate_Sales",
CALCULATE (
MAX ( 'Sales'[Order Date] )
,REMOVEFILTERS ( 'Sales' )
,VALUES ( Sales[CustomerKey] ) --Comment this line to get full Calendar dates
)
)
ORDER BY 'Date'[Date] DESC
Link to the playground to replicate the problem:
https://dax.do/JQ83Voo6Ds93B1/
Commenting the line ,VALUES ( Sales[CustomerKey] )
results in getting all 'Date'[Date] until 2011-12-31 (as desired), however, the function does not return the last date distinct for each Customer. Uncommenting it, returns correct results but shrinks the Date table to the max of Sales table date.
Is it possible to resolve the problem without wrapping SUMMARIZECOLUMNS with ADDMISSINGITEMS?
+------------+--------------------+-----------------+-----------------+
| Date[Date] | Sales[CustomerKey] | __MaxDate_Sales | Status |
+------------+--------------------+-----------------+-----------------+
| 2011-12-31 | 123 | 2009-12-31 | Current result |
| 2011-12-31 | 123 | 2007-01-02 | Expected result |
| 2011-12-31 | 998 | 2007-08-31 | Expected result |
+------------+--------------------+-----------------+-----------------+
If you comment the line above, then the current result is the same for any client. The Date table is 2 years longer than the Sales table.
Upvotes: 0
Views: 604
Reputation: 40304
Is this what you're after?
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Date],
'Sales'[CustomerKey],
"__MaxDate_Sales",
CALCULATE (
MAX ( 'Sales'[Order Date] ),
REMOVEFILTERS ( 'Date' )
)
)
Upvotes: 1