SUMMARIZECOLUMNS/SUMMARIZE/Row functions. Previous month values are blank with SUMMARIZE function.

[KoppulaPSR]

Dear Experts,

Greetings for the day.

I am trying to add Drill Down actions in my SSAS Tabular Cube, under "Detail Rows Expression". Everything working in expected way in drilldown actions, except with one measure, which is Previous Month. My requirment, in drilldown action, need to show my Current month sales as well Previous month sales. For some reason Previous Month Sales showing as Blank when i use Summarize function, whereas i am not able to use "SummarizeColumns" function "Detail Rows Expression", where i am getting context error.

Kinldy advise how can i fullfill my client requirment.

Below is my DAX Query prepared on Adventure Works.

EVALUATE 
CALCULATETABLE (
    SUMMARIZECOLUMNS(
	--'Customer'[First Name],
        "InternetSales",[Drvd_Internet_Total_Sales],
        "Previous Month",CALCULATE ([Drvd_Internet_Total_Sales],PREVIOUSMONTH ('Date'[Date]))    ),
    'Date'[Date] =DATE ( 2013, 12, 01 )  )

GO
EVALUATE 
CALCULATETABLE (
    SUMMARIZE('Internet Sales',
	--'Customer'[First Name],
        "InternetSales",[Drvd_Internet_Total_Sales],
        "Previous Month",CALCULATE ([Drvd_Internet_Total_Sales],PREVIOUSMONTH ('Date'[Date]))    ),
    'Date'[Date] =DATE ( 2013, 12, 01 )  )

GO
EVALUATE 
CALCULATETABLE (
    ROW("InternetSales",[Drvd_Internet_Total_Sales],
        "Previous Month",CALCULATE ([Drvd_Internet_Total_Sales],PREVIOUSMONTH ('Date'[Date]))    ),
    'Date'[Date] =DATE ( 2013, 12, 01 )  )

[ThxAlot · Accepted solution]

SUMMARIZE(
    'Internet Sales',
    "InternetSales", CALCULATE( [Drvd_Internet_Total_Sales], 'Date'[Date] = DATE( 2013, 12, 01 ) ),
    "Previous Month",
        CALCULATE(
            [Drvd_Internet_Total_Sales],
            CALCULATETABLE(
                PREVIOUSMONTH( 'Date'[Date] ),
                'Date'[Date] = DATE( 2013, 12, 01 )
            )
        )
)

[KoppulaPSR]

Thank you for quick response and modifying the query. Same Query working good, when we hard code the data. However, when i tried to replace hardcoded date with column 'Date'[Date] and tested from drilldown action, it's again througing the balnk value for Previous Month Measure. 

For now i have modified my code as per below and it's giving correct results in my drilldown actions.

EVALUATE
VAR _Dates = DISTINCT('Customer'[First Name])
VAR _Details =  ADDCOLUMNS (
		_Dates,
		"Sales", [Drvd_Internet_Total_Sales],
        "SalesPM", CALCULATE ( [Drvd_Internet_Total_Sales], PREVIOUSMONTH('Date'[Date] ) ),
		--"SalesPMTblr", [Drvd_Internet_Total_Sales_PM] 
    )
VAR Result =
	FILTER (
		_Details,		
		NOT (
			ISBLANK ( [Sales] ) && ISBLANK ( [SalesPM] ) 
				)
				)
RETURN Result

Found this code from below link. Hope that helps to others.

https://www.sqlbi.com/articles/controlling-drillthrough-in-excel-pivottables-connected-to-power-bi-o... (#6)