Need help on custom table/matrix format

Hi Sunkari,

 

I'd like to suggest you create four measures to calculate these result, then drag them to matrix with Period as Rows field, date as Columns field.

 

Perv Month = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),MAX([Date].[MonthNo])-1,MAX([Date].[Day]))
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&FORMAT([Date],"yyyy mmm")=FORMAT(calculateDate,"yyyy mmm")),[Amount])

Perv 2 Month = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),MAX([Date].[MonthNo])-2,MAX([Date].[Day]))
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&[Date].[MonthNo]=MONTH(calculateDate)&&[Date].[Year]=YEAR(calculateDate)),[Amount])

Perv 3 Months = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),MAX([Date].[MonthNo])-3,MAX([Date].[Day]))
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&[Date].[MonthNo]=MONTH(calculateDate)&&[Date].[Year]=YEAR(calculateDate)),[Amount])

YTD = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),1,1)
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&[Date]>=calculateDate&&[Date]<=MAX(Smaple[Date])),[Amount])

 

 

 

BTW, current table/matrix not support auto summary the previous value.

 

Regards,

Xiaoxin Sheng