CODEWITHSUNDEEP
powerbidax
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6960

SUMMARIZECOLUMNS to return results for all future dates

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

Answers (1)

Alexis Olson
Alexis Olson

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

Related Questions